[GENERAL] Function which returns record

[dparent]

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.


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Runtime error when calling function from .NET ( Function returns record)

[dparent]

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-when-calling-function-from-.NET-%28-Function-returns-record%29-tf3014812.html#a8372092
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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