I am looking to have the select list passed into a function at runtime and
use this select list to build SQL to execute, for example:
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 (Core Lab's
Postgres data provider), we get a runtime error (similar to the error you
would get if you ran the following: select * from test2('feedid')).
The idea is to create one generic search which build's the select to execute
from metadata - the catch is that the select list could be anything and this
gets built at runtime meaning that we don't want to have the RETURN type of
the function to be TYPE, TABLE or VIEW in order to maximize flexibility and
re-use.
All help is appreciated.
Thanks,
David
--
View this message in context: http://www.nabble.com/Function-which-returns-record-tf2961907.html#a8286937
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
