>> >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