Обсуждение: Documentation regarding %ROWTYPE in PL/PgSQL
I recently referred to the manual (section 23.3) to work out how to write a PL/PgSQL function that accepted a row as a parameter. Reading between a few lines I got the impression that the manual suggested something like: CREATE or REPLACE myfunc( tablename%ROWTYPE ) RETURNS ... When I finally got my function working, I found I had: CREATE or REPLACE myfunc( tablename ) RETURNS ... This is brilliant :-), and in fact the manual foreshadows it: "although one might expect a bare table name to work as a type declaration, it won't be accepted within <application>PL/pgSQL</application> functions." So I guess the manual needs a little update there, or perhaps the syntax that I used only works in the variable list, so I was lucky. I would happily supply a patch to the documentation myself, except that I don't really know what the correct answer is! The docs get a bit hazy in this area regarding the differences between function parameters, declared variables and declared aliases. If someone wants to supply me with the lowdown (or tell me who they are and I can quiz them :-) then I will produce a patch for this area. I'm doing this under 7.2.1, but I've checked CVS as far as I am able and this appears not to have changed. Regards, Andrew. -- -------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Are you enrolled at http://schoolreunions.co.nz/ yet?
Andrew McMillan <andrew@catalyst.net.nz> writes: > Reading between a few lines I got the impression that the manual > suggested something like: > CREATE or REPLACE myfunc( tablename%ROWTYPE ) RETURNS ... > When I finally got my function working, I found I had: > CREATE or REPLACE myfunc( tablename ) RETURNS ... > This is brilliant :-), and in fact the manual foreshadows it: > "although one might expect a bare table name to work as a type > declaration, it won't be accepted within > <application>PL/pgSQL</application> functions." IMHO, %ROWTYPE is an Oracle-ism that we support in plpgsql functions for compatibility's sake. It should work to just use the name of the composite type (= name of the table). But there's at least one place where plpgsql currently requires the %ROWTYPE marker, though I forget the details. The variant that is supported in CREATE FUNCTION argument and result declarations (outside the function body) is "tablename%TYPE" and "tablename.fieldname%TYPE". I have no idea how compatible that is with Oracle, though I believe it was suggested by someone who wanted to port Oracle code. > I would happily supply a patch to the documentation myself, except that > I don't really know what the correct answer is! The docs get a bit hazy > in this area regarding the differences between function parameters, > declared variables and declared aliases. I'm not sure either. A little experimentation seems called for. regards, tom lane
On Tue, 2002-05-28 at 04:59, Tom Lane wrote: > Andrew McMillan <andrew@catalyst.net.nz> writes: > > Reading between a few lines I got the impression that the manual > > suggested something like: > > CREATE or REPLACE myfunc( tablename%ROWTYPE ) RETURNS ... > > When I finally got my function working, I found I had: > > CREATE or REPLACE myfunc( tablename ) RETURNS ... > > This is brilliant :-), and in fact the manual foreshadows it: > > "although one might expect a bare table name to work as a type > > declaration, it won't be accepted within > > <application>PL/pgSQL</application> functions." > > IMHO, %ROWTYPE is an Oracle-ism that we support in plpgsql functions > for compatibility's sake. It should work to just use the name of the > composite type (= name of the table). But there's at least one place > where plpgsql currently requires the %ROWTYPE marker, though I forget > the details. Well, based on what you say here, and my passing encounters with Oracle in the past, I can contrive an example function which seems to hit all of the points necessary to show someone a way to use these things: CREATE OR REPLACE FUNCTION plpgsql_t1( constituents, INT4 ) RETURNS TEXT AS ' DECLARE c ALIAS FOR $1; default_centre ALIAS FOR $2; addressee TEXT; cc centres%ROWTYPE; fullname constituents.pr_first_name%TYPE; BEGIN SELECT * INTO cc FROM centres WHERE centres.centre_id = c.primary_centre_id; IF NOT FOUND THEN SELECT * INTO cc FROM centres WHERE centres.centre_id = default_centre; END IF; fullname = c.pr_first_name || '' '' || c.pr_last_name ; RETURN fullname || '', '' || cc.centre_name; END; ' LANGUAGE plpgsql ; This works fine against 7.2.1 : pcno=# select plpgsql_t1(constituents, 5) from constituents limit 7; plpgsql_t1 ------------------------------------------------------ Hayley Campbell, Whangarei Parents Centre Erin Smith, Wellington South Parents Centre Rachel Dawson, Tauranga Parents Centre Jacquelyn Satherley, Palmerston North Parents Centre Natalie Tankersley, Palmerston North Parents Centre Joy Tavinor, Whangarei Parents Centre Nicola Gee, Wellington South Parents Centre (7 rows) So unless anyone has anything to add I will rustle up an appropriate patch for the docs that tries to make all this a bit clearer. > The variant that is supported in CREATE FUNCTION argument and result > declarations (outside the function body) is "tablename%TYPE" and > "tablename.fieldname%TYPE". I have no idea how compatible that is > with Oracle, though I believe it was suggested by someone who wanted > to port Oracle code. Yes, I seem to recall that is compatible with Oracle except I am not quite so sure about 'tablename%TYPE' - perhaps some Oracle PL/SQL guru can confirm or deny. In the above example if I change: cc centres%ROWTYPE; to either: cc centres%TYPE; cc centres; I get errors. Likewise I appear to have to supply %TYPE to the field, and I can't supply either %ROWTYPE or %TYPE within the parameter definition without an error either. In other words there may be a variant of the above that works, but I haven't been able to find it. > > > I would happily supply a patch to the documentation myself, except that > > I don't really know what the correct answer is! The docs get a bit hazy > > in this area regarding the differences between function parameters, > > declared variables and declared aliases. > > I'm not sure either. A little experimentation seems called for. Experimentation done - now for a patch. Is it a good idea to provide an example (such as the above), or should I just try and describe the behaviour? Thanks, Andrew. -- -------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Are you enrolled at http://schoolreunions.co.nz/ yet?
Andrew McMillan <andrew@catalyst.net.nz> writes: > ... I can't supply either %ROWTYPE or %TYPE within the parameter > definition without an error either. Looks like I was mistaken: table.field%type works, but table%type does not: test72=# create table foo (f1 int, f2 text); CREATE test72=# create function foo(foo.f1%type) returns int as 'select $1' test72-# language sql; NOTICE: foo.f1%TYPE converted to int4 CREATE test72=# create function foo(foo%type) returns text as 'select $1.f2' test72-# language sql; ERROR: parser: parse error at or near "%" So you must do it as: test72=# create function foo(foo) returns text as 'select $1.f2' test72-# language sql; CREATE > Is it a good idea to provide an example (such as the above), or should I > just try and describe the behaviour? Examples are generally good things ... regards, tom lane
On Wed, 2002-05-29 at 01:41, Tom Lane wrote: > > > Is it a good idea to provide an example (such as the above), or should I > > just try and describe the behaviour? > > Examples are generally good things ... OK, the attached documentation patch provides some simple examples of use of tablename as a parameter, %ROWTYPE and %TYPE. In the end I decided that the documentation is literally correct, but hard to follow without any examples explicitly showing the use of a table name as a parameter. Cheers, Andrew. -- -------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Are you enrolled at http://schoolreunions.co.nz/ yet?
Вложения
Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --------------------------------------------------------------------------- Andrew McMillan wrote: > On Wed, 2002-05-29 at 01:41, Tom Lane wrote: > > > > > Is it a good idea to provide an example (such as the above), or should I > > > just try and describe the behaviour? > > > > Examples are generally good things ... > > OK, the attached documentation patch provides some simple examples of > use of tablename as a parameter, %ROWTYPE and %TYPE. > > In the end I decided that the documentation is literally correct, but > hard to follow without any examples explicitly showing the use of a > table name as a parameter. > > Cheers, > Andrew. > -- > -------------------------------------------------------------------- > Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington > WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St > DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 > Are you enrolled at http://schoolreunions.co.nz/ yet? [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Patch applied. Thanks. --------------------------------------------------------------------------- Andrew McMillan wrote: > On Wed, 2002-05-29 at 01:41, Tom Lane wrote: > > > > > Is it a good idea to provide an example (such as the above), or should I > > > just try and describe the behaviour? > > > > Examples are generally good things ... > > OK, the attached documentation patch provides some simple examples of > use of tablename as a parameter, %ROWTYPE and %TYPE. > > In the end I decided that the documentation is literally correct, but > hard to follow without any examples explicitly showing the use of a > table name as a parameter. > > Cheers, > Andrew. > -- > -------------------------------------------------------------------- > Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington > WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St > DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 > Are you enrolled at http://schoolreunions.co.nz/ yet? [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026