Substitute a variable in PL/PGSQL.

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?

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

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

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

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.