By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,834 Members | 2,265 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,834 IT Pros & Developers. It's quick & easy.

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

P: n/a
I am following along with the pl/pgsql docs here:
http://www.postgresql.org/docs/curre...larations.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&...alup/home&ST=1
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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/curre...larations.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&...alup/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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #2

P: n/a
"Lee Harr" <mi*****@hotmail.com> writes:
I am following along with the pl/pgsql docs here:
http://www.postgresql.org/docs/curre...larations.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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.