Execute PG function via Linked Server

[lcohan]

I'm trying to execute a function via linked server in SQL Server like below and getting an error:

SELECT * FROM PGNP_OLTP.MyQAdb.[public].arc_orders();

Msg 4122, Level 16, State 1, Line 1
Remote table-valued function calls are not allowed.


The function call works fine if executed directly in PG or via SSIS but not in SQL query via linked server as per PGNP documentation. I'm using version 1.4 64bits on SQL Server 2008 SP2 connecting to PG 9.0 on Linux.

Here is my provider string:
'PROVIDER=PGNP.1;USERID=pg_dev;SSL=allow;PORT=5432;FORCEUTF8=ON;NESTED_TRANS=ON;CNV_SPECIAL_FLTVAL=ON;
BULK_METHOD=COPY;BULK_INSERT=1000;'

Is there any way to run these function calls from SQL directly? My intention was to convert all lingering code into SQL code objects (stored procs) and execute them from SQL jobs where we have history,logging,execution stats but this error means no PG function calls are allowed?
The function actually returns VOID and all it does it backs-up orders then delets them so there is no actual record set returned by it.

After I tried similar syntax to SSIS except the full (four) name qualifier I gett a different error:

SELECT PGNP_OLTP.MyQAdb.[public].arc_orders();

Msg 344, Level 16, State 1, Line 1
Remote function reference 'PGNP_OLTP.MyQAdb.public.arc_orders' is not allowed, and the column name 'PGNP_OLTP' could not be found or is ambiguous.

So I guess my basic simple question is:

Is there any way to run PG function in SQL code via Linked Servers (as documented by PGNP) using full (four) name qualifier?

[Moderator]

I guess your function is declared with "RETURNS SETOF record AS..." If that is the case then the correct way of calling it would be:
SELECT * FROM OPENQUERY(PGNP_OLTP, 'SELECT* FROM arc_orders() AS (col1 integer, col2 varchar(100), ...)')

If the above does not help, please send DDL script for arc_orders(), and we will try to reproduce the issue.

[lcohan]

My function returns void as per code posted below and I tried:

SELECT * FROM OPENQUERY(PGNP_OLTP, 'SELECT* FROM arc_orders()')

but got the error:

OLE DB provider "PGNP" for linked server "PGNP_OLTP" returned message "Undefined procedure arc_orders".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT* FROM arc_orders()" for execution against OLE DB provider "PGNP" for linked server "PGNP_OLTP".


The function code is posted below and I know the permissions to execute it are there because through ODBC driver configured for the same user it works. The issue is that we want to move away from the ODBC and use OLEDB where we should be able to access all objects by full(four) name qualifier via linked server:

CREATE OR REPLACE FUNCTION arc_orders()
RETURNS void AS
$BODY$
BEGIN

truncate table arc_batch_orders;
insert into arc_batch_orders
select distinct id from public.orders
where (modified_at < now() - INTERVAL '60 days') AND status_type_id = -1;

delete from arc_batch_orders where id in (select distinct order_id from public.sec_dtran);
delete from arc_batch_orders where id in (select distinct order_id from public.sec_token);

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION arc_batch_orders() OWNER TO dbo;


Another function I tried that I know returns SET OFF ... and I used the exact list fron the TYPE used by the SET OF... with same error like above and again I know 1000% that the user calling it has rights to do it:


Another function I tried that I know returns SET OFF ... and I used the exact list fron the TYPE used by the SET OF... with same error like above and again I know 1000% that the user calling it has rights to do so and same as the other one - it is all ok via ODBC configured with same PG login:

SELECT * FROM OPENQUERY(PGNP_OLTP, 'SELECT* FROM carts_saved() AS (id integer,user character(100),user_id integer,modified_at date)')

with error

OLE DB provider "PGNP" for linked server "PGNP_OLTP" returned message "Undefined procedure carts_saved".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT* FROM carts_saved() AS (id integer,user character(100),user_id integer,modified_at date)" for execution against OLE DB provider "PGNP" for linked server "PGNP_OLTP".


The function code is:


CREATE FUNCTION carts_saved()
RETURNS SETOF carts_saved AS
$BODY$

SELECT distinct
cs.id,
cs.user,
c.user_id,
c.modified_at
FROM
search_queue s
inner join carts_queue cs on s.table_id = 99 and s.row_id = cs.id
INNER JOIN carts c ON cs.id = c.id;

$BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION carts_saved() OWNER TO dbo;

[lcohan]

Hmmm.....we made an interesting/intriguing discovery but at least it is a workaround until a function call can be made via PGNP driver/SQL linked server by using full(four) name qualifier. We tried to create a view on the top of function and select from that view instead in SQL and...voile! it all works as it should work with function call directly.

In PG database I create following view:

CREATE VIEW vw_carts_saved AS
select * from carts_saved();
ALTER TABLE vw_carts_saved OWNER TO dbo;

then I can run in SQL:

SELECT * from PGNP_OLTP.MyDB.[public].vw_carts_saved;

and get the expected result.

[Moderator]

The correct ways of calling a procedure returning "void" is
EXEC( 'SELECT arc_orders()') AT PGNP_OLTP; or
EXEC( 'SELECT public.arc_orders()') AT PGNP_OLTP;

How is the type 'carts_saved' defined for the clause 'SETOF carts_saved'? Please send it to us, so we can reproduce the issue.

Thank you!

[lcohan]

I tried your suggestion but no luck unfortunately...

I tried EXEC( 'SELECT public.arc_orders()') AT PGNP_OLTP;
and got the error:

Msg 7411, Level 16, State 1, Line 1
Server 'PGNP_OLTP' is not configured for RPC.

Enabled RPC under linked server properties tried the same statement again and this time I got:


OLE DB provider "PGNP" for linked server "PGNP_OLTP" returned message "ERROR: function public.arc_orders() does not exist
LINE 2: SELECT public.arc_orders()
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'PGNP_OLTP'.



I tried to add the database name (I know PG is case sensitive so MyDB is the actual DB name) in front of the public schema name like: EXEC( 'SELECT MyDB.public.arc_orders()') AT PGNP_OLTP;
And this time I got:


OLE DB provider "PGNP" for linked server "PGNP_OLTP" returned message "** ERROR: Failed to parse statement: SELECT OneShopSecureCartQA.public.arc_orders()".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'PGNP_OLTP'.



On another note here's the type for the function:

CREATE TYPE carts_saved AS
(id integer,
user character(100),
userid_id integer,
modified_at timestamp without time zone);
ALTER TYPE carts_saved OWNER TO dbo;

[Moderator]

The error "function public.arc_orders() does not exist" could be caused by a bug in an older version of the PGNP OLEDB provider. Could you check what build is used? Version/build can be viewed with call to EXEC( 'SELECT pgnp_getlicenseinfo()') AT PGNP_OLTP.

I'm working on the bug in handling 'carts_saved' type. Will update you shortly. Thank you!

[lcohan]

Sorry for my late reply as we were bussy re-writing code using old ODBC data DSN's to the new PGNP olden provider. Today we managed to upgrade our production to use the latest version PGNP-Postgres-SE-1.4.0.3076 that we recently got and it includes a few bug fixes for errors we encountered.

[lcohan]

"The error "function public.arc_orders() does not exist" could be caused by a bug in an older version of the PGNP OLEDB provider."

I actually can't understand how this statement may work without a catalog or db name because PGNP_OLTP linked server has 9 user catalogs(databases) in this specific case so I can’t imagine how it would know where is the function public.arc_orders() and all underlying tables?

I know there is a default catalog(database) that could be changed in the SQL Linked Server from the default @catalog=N'postgres' but that means we would need one Linked Server per accessed database (as the default) but this defeats the purpose of accessing remote objects via linked servers by full (four) name qualifiers as documented on page 19/20 of http://www.pgoledb.com/downloads/Developer%20Manual.pdf

SELECT * FROM “MYLINKEDSVR”...”contacts”
INSERT * INTO “MYLINKEDSVR”...”contacts” VALUES(‘John’, ‘Smith’, 1988)

Kind regards.

[Moderator]

If database name is not used as in the query:
SELECT * FROM “MYLINKEDSVR”...”contacts”
then the OLEDB provider uses the database from the connection string (it was specified when linked server was created).

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.