I have a function which returns TYPE RECORD and receives a string. The passed
string is used to build a dynamic SQL statement and the passed string is the
SELECT LIST of the built dynamic SQL statement.

The call works fine from Postgres SQL (PL/PG SQL) but when I make the call
from .NET (Core labs data provider), I get a runtime error.

Any ideas on how to get this to work ? One solution we came up with is to
use a REFCURSOR or to return back a string to .NET and have .NET execute the
string (ie. select * from test2('feedid') as (name bigint).

We were hoping for an easier &/or better solution.

Example function:
CREATE or REPLACE FUNCTION "public"."test2"(
IN "_sfieldlist" varchar)
RETURNS SETOF "pg_catalog"."record" AS
$BODY$
DECLARE
v_feed RECORD;
v_sfieldlist varchar(512);
BEGIN
v_sfieldlist := _sfieldlist;

FOR v_feed IN EXECUTE '
SELECT '||v_sfieldlist||'
FROM feed'
LOOP
RETURN NEXT v_feed;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE SECURITY DEFINER;

This works just ducky from PL/PG SQL when I run a select something like the
following:
select * from test2('feedid') as (name bigint);

However, when I make a straight call to this function from .NET we get a
runtime error (similar to the error you would get if you ran the following:
select * from test2('feedid')).
--
View this message in context: http://www.nabble.com/Runtime-error-....html#a8372092
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

dparent <davidp@channeladvisor.com> writes:
> I have a function which returns TYPE RECORD and receives a string. The passed
> string is used to build a dynamic SQL statement and the passed string is the
> SELECT LIST of the built dynamic SQL statement.

> The call works fine from Postgres SQL (PL/PG SQL) but when I make the call
> from .NET (Core labs data provider), I get a runtime error.

It sounds a bit like the .net provider is editorializing on your query :-(.
I'd suggest enabling statement logging on the server to confirm or
refute that. If it is indeed changing your SQL then complaining to its
authors seems the next step.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq