Thread: pl/pgsql docs 37.4.3. Row Types -- how do I use this function?

I am following along with the pl/pgsql docs here:
http://www.postgresql.org/docs/current/static/plpgsql-declarations.html

In section 37.4.3. Row Types I have altered the function slightly
(I finished the where ... clause) :


CREATE or REPLACE FUNCTION use_two_tables(tablename) RETURNS text AS '
DECLARE
    in_t ALIAS FOR $1;
    use_t table2name%ROWTYPE;
BEGIN
    SELECT * INTO use_t FROM table2name WHERE f1 = ''a'';
    RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;
END;
' LANGUAGE plpgsql;



Just before that, I created two tables:

CREATE TABLE tablename(
    f1 text,
    f2 text,
    f3 text,
    f4 text,
    f5 text,
    f6 text,
    f7 text
);
CREATE TABLE table2name(
    f1 text,
    f2 text,
    f3 text,
    f4 text,
    f5 text,
    f6 text,
    f7 text
);

and put in some data:

insert into tablename values ('a', 'bb', 'ccc', 'dddd', 'eee', 'ff', 'g');
insert into tablename values ('aa', 'bbb', 'cccc', 'ddd', 'eeee', 'fff',
'gg');
insert into table2name values ('aaaa', 'bbb', 'cc', 'd', 'ee', 'fff',
'gggg');
insert into table2name values ('a', 'bb', 'ccc', 'dddd', 'eeeee', 'ffffff',
'ggggggg');


Now, how do I call the function?

CREATE FUNCTION
# select use_two_tables(tablename);
ERROR:  column "tablename" does not exist
# select use_two_tables(f1);
ERROR:  column "f1" does not exist
# select use_two_tables(table2name);
ERROR:  column "table2name" does not exist

_________________________________________________________________
Check out the new MSN 9 Dial-up � fast & reliable Internet access with prime
features! http://join.msn.com/?pgmarket=en-us&page=dialup/home&ST=1

Hello,

Unless your function parameter is an integer you must quote it... eq:

select use_two_tables('tablename');

Sincerely,


Joshua D. Drake


Lee Harr wrote:

> I am following along with the pl/pgsql docs here:
> http://www.postgresql.org/docs/current/static/plpgsql-declarations.html
>
> In section 37.4.3. Row Types I have altered the function slightly
> (I finished the where ... clause) :
>
>
> CREATE or REPLACE FUNCTION use_two_tables(tablename) RETURNS text AS '
> DECLARE
> in_t ALIAS FOR $1;
> use_t table2name%ROWTYPE;
> BEGIN
> SELECT * INTO use_t FROM table2name WHERE f1 = ''a'';
> RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;
> END;
> ' LANGUAGE plpgsql;
>
>
>
> Just before that, I created two tables:
>
> CREATE TABLE tablename(
> f1 text,
> f2 text,
> f3 text,
> f4 text,
> f5 text,
> f6 text,
> f7 text
> );
> CREATE TABLE table2name(
> f1 text,
> f2 text,
> f3 text,
> f4 text,
> f5 text,
> f6 text,
> f7 text
> );
>
> and put in some data:
>
> insert into tablename values ('a', 'bb', 'ccc', 'dddd', 'eee', 'ff',
> 'g');
> insert into tablename values ('aa', 'bbb', 'cccc', 'ddd', 'eeee',
> 'fff', 'gg');
> insert into table2name values ('aaaa', 'bbb', 'cc', 'd', 'ee', 'fff',
> 'gggg');
> insert into table2name values ('a', 'bb', 'ccc', 'dddd', 'eeeee',
> 'ffffff', 'ggggggg');
>
>
> Now, how do I call the function?
>
> CREATE FUNCTION
> # select use_two_tables(tablename);
> ERROR: column "tablename" does not exist
> # select use_two_tables(f1);
> ERROR: column "f1" does not exist
> # select use_two_tables(table2name);
> ERROR: column "table2name" does not exist
>
> _________________________________________________________________
> Check out the new MSN 9 Dial-up — fast & reliable Internet access with
> prime features! http://join.msn.com/?pgmarket=en-us&page=dialup/home&ST=1
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

"Lee Harr" <missive@hotmail.com> writes:
> I am following along with the pl/pgsql docs here:
> http://www.postgresql.org/docs/current/static/plpgsql-declarations.html

> Now, how do I call the function?

I believe you want

select use_two_tables(tablename.*) from tablename;

"foo.*" is the locution for referring to the whole-row value coming from
table foo in a select.

            regards, tom lane

>>I am following along with the pl/pgsql docs here:
>>http://www.postgresql.org/docs/current/static/plpgsql-declarations.html
>
>>Now, how do I call the function?
>
>I believe you want
>
>select use_two_tables(tablename.*) from tablename;
>
>"foo.*" is the locution for referring to the whole-row value coming from
>table foo in a select.
>


A ha!  That's the one!
Here is a complete working example for those playing along at home...


CREATE TABLE ta1(
    f1 text,
    f2 text,
    f3 text,
    f4 text,
    f5 text,
    f6 text,
    f7 text
);
CREATE TABLE ta2(
    f1 text,
    f2 text,
    f3 text,
    f4 text,
    f5 text,
    f6 text,
    f7 text
);
insert into ta1 values ('a', 'b', 'c', 'd', 'e', 'f', 'g');
insert into ta1 values ('aa', 'bb', 'cc', 'dd', 'ee', 'ff', 'gg');
insert into ta1 values ('aaa', 'bbb', 'ccc', 'ddd', 'eee', 'fff', 'ggg');
insert into ta2 values ('z', 'y', 'x', 'w', 'v', 'u', 't');
insert into ta2 values ('zz', 'yy', 'xx', 'ww', 'vv', 'uu', 'tt');

CREATE or REPLACE FUNCTION use_two_tables(ta1) RETURNS text AS '
DECLARE
    in_t ALIAS FOR $1;
    use_t ta2%ROWTYPE;
BEGIN
    SELECT * INTO use_t FROM ta2 WHERE f1 = ''z'';
    RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;
END;
' LANGUAGE plpgsql;



# select use_two_tables(ta1.*) from ta1;
use_two_tables
----------------
axet
aaxeet
aaaxeeet
(3 rows)


Thank you for your help.

_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail