Обсуждение: Substitute a variable in PL/PGSQL.
Hi,
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?
Test case:
CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text,
col6 text, col7 text, col8 text, col9 text, col10 text);
INSERT INTO test VALUES ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j');
INSERT INTO test VALUES ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J');
INSERT INTO test VALUES ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10');
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;
test=> select * from testfunc();
testfunc
-----------
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
(30 rows)
test=>
Or:
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
bob RECORD;
ted TEXT;
BEGIN
FOR i IN 1..10 LOOP
ted := 'col' || i;
FOR bob IN SELECT ted FROM test LOOP
RETURN NEXT bob;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test=> select * from testfunc();
testfunc
----------
(col1)
(col1)
(col1)
(col2)
(col2)
(col2)
(col3)
(col3)
(col3)
(col4)
(col4)
(col4)
(col5)
(col5)
(col5)
(col6)
(col6)
(col6)
(col7)
(col7)
(col7)
(col8)
(col8)
(col8)
(col9)
(col9)
(col9)
(col10)
(col10)
(col10)
(30 rows)
test=>
Or is there another way other than using another procedural language.
Thanks - Steve M.
On 12:33 am 07/22/08 Steve Martin <steve.martin@nec.co.nz> wrote: > Hi, > > 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. Are you trying to do a generic function that would work for any table or for just a single table? Is it goint to run against a large data set?
You can do it in straight sql like so. SELECT (array[col1, col2, col3, col4, col5, col6, col7, col8, col9, col10])[i] FROM test t, generate_series(1,10) i Art
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.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
On Wed, Jul 23, 2008 at 4:08 AM, Klint Gore <kgore4@une.edu.au> 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;
here is a way to do it with record variables...no inner loop but
doesn't the column names. with a little work you could add those with
some queries to information_schema (i don't think it's worth it
though).
create or replace function ff(tablename text) returns setof text as
$$
declare
r record;
begin
for r in
execute 'select record_out(' || tablename || ') as f' ||
' from ' || tablename loop
return next r.f;
end loop;
end;
$$ language plpgsql;
merlin
Steve Martin wrote:
> Hi,
>
> 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?
>
> Test case:
>
> CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5
> text, col6 text, col7 text, col8 text, col9 text, col10 text);
> INSERT INTO test VALUES ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i',
> 'j');
> INSERT INTO test VALUES ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',
> 'J');
> INSERT INTO test VALUES ('1', '2', '3', '4', '5', '6', '7', '8', '9',
> '10');
>
> 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;
>
> test=> select * from testfunc();
> testfunc -----------
> bob.col1
> bob.col2
> bob.col3
> bob.col4
> bob.col5
> bob.col6
> bob.col7
> bob.col8
> bob.col9
> bob.col10
> bob.col1
> bob.col2
> bob.col3
> bob.col4
> bob.col5
> bob.col6
> bob.col7
> bob.col8
> bob.col9
> bob.col10
> bob.col1
> bob.col2
> bob.col3
> bob.col4
> bob.col5
> bob.col6
> bob.col7
> bob.col8
> bob.col9
> bob.col10
> (30 rows)
>
> test=>
>
> Or:
> CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
> DECLARE bob RECORD;
> ted TEXT;
> BEGIN
> FOR i IN 1..10 LOOP
> ted := 'col' || i;
> FOR bob IN SELECT ted FROM test LOOP
> RETURN NEXT bob;
> END LOOP;
> END LOOP;
> RETURN;
> END
> $$ LANGUAGE plpgsql;
> test=> select * from testfunc();
> testfunc ----------
> (col1)
> (col1)
> (col1)
> (col2)
> (col2)
> (col2)
> (col3)
> (col3)
> (col3)
> (col4)
> (col4)
> (col4)
> (col5)
> (col5)
> (col5)
> (col6)
> (col6)
> (col6)
> (col7)
> (col7)
> (col7)
> (col8)
> (col8)
> (col8)
> (col9)
> (col9)
> (col9)
> (col10)
> (col10)
> (col10)
> (30 rows)
>
> test=>
> Or is there another way other than using another procedural language.
>
> Thanks - Steve M.
Found that this function works if I process by column.
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
bob RECORD;
ted TEXT;
may TEXT;
BEGIN
FOR i IN 1..10 LOOP
ted := 'col' || i;
may := ' SELECT ' || ted || ' as col FROM test';
FOR bob IN EXECUTE may LOOP
RETURN NEXT bob.col;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test=> select testfunc as data from testfunc() ;
data
------
a
A
1
b
B
2
c
C
3
d
D
4
e
E
5
f
F
6
g
G
7 d
D
4
e
E
5
f
F
6
g
G
7
h
H
8
i
I
9
j
J
10
(30 rows)
test=>
Any ideas on how to process by row?
Steve Martin
Hi Francisco,
Francisco Reyes wrote:
>On 12:33 am 07/22/08 Steve Martin <steve.martin@nec.co.nz> wrote:
>
>
>>Hi,
>>
>>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.
>>
>>
>
>Are you trying to do a generic function that would work for any table or
>for just a single table?
>
>Is it goint to run against a large data set?
>
>
>
What I am trying to do is find the difference between two tables, one
that stores the
information in a single column, and the other which stores the same data
in multiple
columns.
E.g.
CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text,
col6 text, col7 text, col8 text, col9 text, col10 text);
CREATE TABLE test2(col_data text NOT NULL, some_data text NOT NULL,
other_data text,
CONSTRAINT test2_index PRIMARY KEY(
col_data,
some_data ));
Trying to find data set in test2.col_data that is not in test.col1 to
test.col10.
The data sets are very small, e.g. < 10 000 rows.
Using pl/pgsql. the tried using the pl/pgsql's EXECUTE statement,
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
ted text;
bob RECORD;
BEGIN
FOR bob IN SELECT * FROM test LOOP
FOR i IN 1..10 LOOP
ted := 'bob.col' || i;
EXECUTE 'RETURN NEXT ' || ted;
-- RETURN NEXT bob.col1;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test=> select * from testfunc() ;
ERROR: syntax error at or near "RETURN" at character 1
QUERY: RETURN NEXT bob.col1
CONTEXT: PL/pgSQL function "testfunc" line 8 at execute statement
LINE 1: RETURN NEXT bob.col1
^
test=>
Note Postgres version 8.1.10.
Regards
Steve Martin
Merlin Moncure wrote:
>On Wed, Jul 23, 2008 at 4:08 AM, Klint Gore <kgore4@une.edu.au> wrote:
>
>
>here is a way to do it with record variables...no inner loop but
>doesn't the column names. with a little work you could add those with
>some queries to information_schema (i don't think it's worth it
>though).
>
>create or replace function ff(tablename text) returns setof text as
>$$
> declare
> r record;
> begin
> for r in
> execute 'select record_out(' || tablename || ') as f' ||
> ' from ' || tablename loop
> return next r.f;
> end loop;
> end;
>$$ language plpgsql;
>
>merlin
>
>
>
Hi Merlin,
Where can I find out more on the record_out function?
Steve M.
> What I am trying to do is find the difference between two tables, one
> that stores the
> information in a single column, and the other which stores the same
data
> in multiple
> columns.
>
> E.g.
> CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5
text,
> col6 text, col7 text, col8 text, col9 text, col10 text);
> CREATE TABLE test2(col_data text NOT NULL, some_data text NOT NULL,
> other_data text,
> CONSTRAINT test2_index PRIMARY
KEY(
> col_data,
> some_data ));
>
> Trying to find data set in test2.col_data that is not in test.col1 to
> test.col10.
>
FINALLY you get to the requirements. Next time, just ask a question
like the above. You were asking how to solve a technical problem that
didn't relate to the actual business need.
Here are three ways to skin this cat.
--version 1
select col_data from test2
except
select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') ||
coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') ||
coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') ||
coalesce(col10, '')
from test
--version 2
select col_data
from test2 t2
where not exists (select null
from test t
where t2.col_data = coalesce(t.col1, '') ||
coalesce(t.col2, '') ||
coalesce(t.col3, '') ||
coalesce(t.col4, '') ||
coalesce(t.col5, '') ||
coalesce(t.col6, '') ||
coalesce(t.col7, '') ||
coalesce(t.col8, '') ||
coalesce(t.col9, '') ||
coalesce(t.col10, ''))
--version 3
select t2.col_data
from test2 t2
left join (select coalesce(col1, '') || coalesce(col2, '') ||
coalesce(col3, '') || coalesce(col4, '') ||
coalesce(col5, '') || coalesce(col6, '') ||
coalesce(col7, '') || coalesce(col8, '') ||
coalesce(col9, '') || coalesce(col10, '') as
col_data
from test) t
on t2.col_data = t.col_data
where t.col_data is null
Jon
Roberts, Jon wrote: >>What I am trying to do is find the difference between two tables, one >>that stores the >>information in a single column, and the other which stores the same >> >> >data > > >>in multiple >>columns. >> >>E.g. >>CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 >> >> >text, > > >>col6 text, col7 text, col8 text, col9 text, col10 text); >>CREATE TABLE test2(col_data text NOT NULL, some_data text NOT NULL, >>other_data text, >> CONSTRAINT test2_index PRIMARY >> >> >KEY( > > >> col_data, >> some_data )); >> >>Trying to find data set in test2.col_data that is not in test.col1 to >>test.col10. >> >> >> > >FINALLY you get to the requirements. Next time, just ask a question >like the above. You were asking how to solve a technical problem that >didn't relate to the actual business need. > >Here are three ways to skin this cat. > >--version 1 >select col_data from test2 >except >select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') || > > coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') || > > coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') || > > coalesce(col10, '') > from test > >--version 2 >select col_data > from test2 t2 > where not exists (select null > from test t > where t2.col_data = coalesce(t.col1, '') || > coalesce(t.col2, '') || > coalesce(t.col3, '') || > coalesce(t.col4, '') || > coalesce(t.col5, '') || > coalesce(t.col6, '') || > coalesce(t.col7, '') || > coalesce(t.col8, '') || > coalesce(t.col9, '') || > coalesce(t.col10, '')) >--version 3 >select t2.col_data > from test2 t2 > left join (select coalesce(col1, '') || coalesce(col2, '') || > coalesce(col3, '') || coalesce(col4, '') || > coalesce(col5, '') || coalesce(col6, '') || > coalesce(col7, '') || coalesce(col8, '') || > coalesce(col9, '') || coalesce(col10, '') as >col_data > from test) t > on t2.col_data = t.col_data > where t.col_data is null > > >Jon > > Thanks Jon for the hints. Steve
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.
artacus@comcast.net wrote: >You can do it in straight sql like so. > >SELECT (array[col1, col2, col3, col4, col5, col6, col7, col8, col9, col10])[i] >FROM test t, generate_series(1,10) i > >Art > > > Hi Art, Thanks for the advice, in my case using arrays was not a option as the data could be null. Steve M.