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

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

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

select use_two_tables('tablename');

Hrm... That does not work either:

# select use_two_tables('tablename');
ERROR: function use_two_tables("unknown") does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
# select use_two_tables('tablename'::text);
ERROR: function use_two_tables(text) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.

I am not really sure... just learning this, but I believe the parameter
type is tablename, right?

CREATE TABLE tablename( ...

CREATE or REPLACE FUNCTION use_two_tables(tablename) RETURNS text AS '
...

Do I need to cast the string to something?

# \df use_two*
List of functions
Result data type | Schema | Name | Argument data types
------------------+--------+----------------+---------------------
text | lee | use_two_tables | tablename
(1 row)

# select use_two_tables('tablename'::tablename);
ERROR: cannot cast type "unknown" to tablename
# select use_two_tables(tablename::tablename);
ERROR: column "tablename" does not exist

Thanks for your help.

_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus

Lee Harr <missive@hotmail.com> said:

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

select use_two_tables('tablename');

Hrm... That does not work either:

# select use_two_tables('tablename');
ERROR: function use_two_tables("unknown") does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
# select use_two_tables('tablename'::text);
ERROR: function use_two_tables(text) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.

Why not just use a text type in your definition?

CREATE or REPLACE FUNCTION use_two_tables(text) RETURNS text AS ...

You can always do a cast inside the procedure if you need to.

Best regards,

Jim Wilson

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

select use_two_tables('tablename');

Hrm... That does not work either:

# select use_two_tables('tablename');
ERROR: function use_two_tables("unknown") does not exist

Why not just use a text type in your definition?

CREATE or REPLACE FUNCTION use_two_tables(text) RETURNS text AS ...

You can always do a cast inside the procedure if you need to.

Ok. I don't really know... I am just trying to follow along with the docs:
http://www.postgresql.org/docs/current/static/plpgsql-declarations.html
in section 37.4.3. Row Types

Is this not the right way to do it? A doc bug maybe?

The docs have this:

CREATE 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 ... ;
RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;
END;
' LANGUAGE plpgsql;

Which I changed to this:

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;

I am really just guessing that I was supposed to create those two tables
tablename and table2name before defining the function.... the section is
not very clear to me.

Actually, this sort of works:

CREATE or REPLACE FUNCTION use_two_tables() RETURNS text AS '
DECLARE
in_t tablename%ROWTYPE;
use_t table2name%ROWTYPE;
BEGIN
SELECT * INTO in_t FROM tablename WHERE f1 = ''a'';
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;

# select use_two_tables();
use_two_tables
----------------
accceeeggggggg
(1 row)

But I don't think it makes the same point that that section of
the docs is trying to make.

Maybe I am not sure what exactly the point is?

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

"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