Search Postgresql Archives

Function which returns record

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux