Execute PG function via Linked Server

General PGNP discussions.

Re: Execute PG function via Linked Server

Postby lcohan » Thu Sep 13, 2012 9:34 am

<<When, however, database name is specified as in the query:
SELECT * FROM “MYLINKEDSVR”.mydb.[public].”contacts”
the OLEDB provider opens connection to "mydb" database, and executes the statement. This is a "nice to have" feature.>>


Actually I am sure that IS a feature of current PGNP OLEDB for tables (not functions) and I can successfully execute each and any of the statements below as documented by both PGNP and SQL:

--in PG database
CREATE TABLE merchant_data
(
merchant_id integer NOT NULL
)
WITH (
OIDS=FALSE
);
ALTER TABLE merchant_data OWNER TO dbo;
GRANT ALL ON TABLE merchant_data TO dbo;

--In SQL - add a linked server using PGNP Driver and execute:
SELECT * FROM PGNP_OLTP.MyDB.[public].merchant_data
INSERT INTO PGNP_OLTP.MyDB.[public].merchant_data (merchant_id) VALUES (123456)
DELETE FROM PGNP_OLTP.MyDB.[public].merchant_data;
SELECT * FROM PGNP_OLTP.MyDB.[public].merchant_data
INSERT INTO PGNP_OLTP.MyDB.[public].merchant_data (merchant_id) VALUES (123456)
SELECT * FROM PGNP_OLTP.MyDB.[public].merchant_data
UPDATE PGNP_OLTP.MyDB.[public].merchant_data SET merchant_id= 999999
SELECT * FROM PGNP_OLTP.MyDB.[public].merchant_data

The problem occurs when you have to run SELECT commands against a PG function because of the SQL restriction and PGNP parse error as mentioned above. The workaround I used was to "fake" the direct calls to the PG function by creating a PG view on the top of the function and then a SQL SELECT from that view works fine for a table as mentioned above.
lcohan
 
Posts: 8
Joined: Wed May 30, 2012 9:05 am

Re: Execute PG function via Linked Server

Postby lcohan » Tue Sep 25, 2012 7:28 am

If is just a "nice to have" functionality for functions why is it working with a table or a view (where by the way all I do is select * from my_pg_function)...but not with a function?
PGNP documentation says (implicitely) that this should work if I'm not mistaken.

Kind Regards.
lcohan
 
Posts: 8
Joined: Wed May 30, 2012 9:05 am

Previous

Return to Miscellaneous

Who is online

Users browsing this forum: No registered users and 2 guests

cron