« Return to Thread: Function which returns record

Function which returns record

by dparent :: Rate this Message:

Reply to Author | View in Thread

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


 « Return to Thread: Function which returns record