Klint Gore wrote:
Steve Martin wrote:
I am trying to create a PL/PGSQL function to return the values of the
fields in a record, e.g. 1 value per row in the output of the function.
How do you substitute a variable?
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE ted varchar;
bob RECORD;
BEGIN
FOR bob IN SELECT * FROM test LOOP
FOR i IN 1..10 LOOP
ted := 'bob.col' || i;
RETURN NEXT ted;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
Or is there another way other than using another procedural language.
Thanks - Steve M.
There's no direct way to reference a particular field in a record
variable where the field name is held in a variable in pl/pgsql.
I.E. if ted = 'col1' there's no way to reference bob.ted to give you
the value of bob.col1.
If you want it easy to code but have to create something for every
table and modify it ever time the table changes
create view test_vertical_table as
select col1::text from test
union all
select col2::text from test
union all
select col3::text from test
union all
select col4::text from test
union all
select col5::text from test
...
If you want to go the generic function route
CREATE OR REPLACE FUNCTION testfunc(text) RETURNS SETOF text AS $$
DECLARE vertTableName alias for $1;
ted text;
bob RECORD;
bill record;
BEGIN
for bill in select table_name, column_name from
information_schema.columns where table_schema = public
and table_name = vertTableName
loop
FOR bob IN execute 'SELECT '||bill.column_name||' as
thiscol FROM '||bill.table_name LOOP
ted := bob.thiscol;
RETURN NEXT ted;
END LOOP;
end loop;
RETURN;
END
$$ LANGUAGE plpgsql;
klint.
Hi Klint,
Thanks for the advice, I found the sql to get the column names useful.
Steve M.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
