I guess the question I have then is how do I return data from a select statement using the record datatype? This is only a test function I was trying to get info from, but my application is based on passing the table name to the function dynamically. How do I do this? The documentation is sketchy when it comes to the record datatype.
You need to specify in your query the column definition that will actually be returned. Here's a simple example:
create table t1 (f1 int, f2 text); insert into t1 values (1,'a'); insert into t1 values (2,'b'); create table t2 (f1 int, f2 float8); insert into t2 values (3, 3.14); insert into t2 values (4, 2.8);
create or replace function getrec(text) returns setof record as ' DECLARE ft record; begin FOR ft IN EXECUTE ''SELECT * FROM '' || $1 LOOP RETURN NEXT ft; END LOOP; return; end; ' LANGUAGE 'plpgsql' VOLATILE;
regression=# SELECT * FROM getrec('t1') AS (f1 int, f2 text); f1 | f2 ----+---- 1 | a 2 | b (2 rows)
regression=# SELECT * FROM getrec('t2') AS (f1 int, f2 float8); f1 | f2 ----+------ 3 | 3.14 4 | 2.8 (2 rows)
HTH,
Joe
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly