Re: [BUGS] Documentation regarding %ROWTYPE in PL/PgSQL
On Tue, 2002-05-28 at 04:59, Tom Lane wrote: > Andrew McMillan <[EMAIL PROTECTED]> 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 > > PL/pgSQL 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-7201MOB: +64(21)635-694OFFICE: +64(4)499-2267 Are you enrolled at http://schoolreunions.co.nz/ yet? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Bug #680: NOCREATETABLE
On Mon, May 27, 2002 at 02:57:06PM -0400, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > I saw a artiche what say that in 7.2 PostgreSQL version, the resource of create a >user with NOCREATETABLE was ready to use. > > I don't know where you saw that, but it has nothing to do with reality. I think he can saw it. It's my old patch for 7.0 and some people use it. I have it for 7.0.2 at ftp://ftp2.zf.jcu.cz/users/zakkr/pg/ But somebody else port to 7.1 and maybe fo 7.2 too -- try google or http://archives.postgresql.org. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Documentation regarding %ROWTYPE in PL/PgSQL
Andrew McMillan <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] plpgsql function behaves strange
see attachment -- Christian Zagrodnick gocept gmbh & co. kg - schalaunische strasse 6 - 06366 koethen/anhalt fon. +49 3496 3099114 fax. +49 3496 3099118 mob. +49 173 9078826 mail [EMAIL PROTECTED] If PostgreSQL failed to compile on your computer or you found a bug that is likely to be specific to one platform then please fill out this form and e-mail it to [EMAIL PROTECTED] To report any other bug, fill out the form below and e-mail it to [EMAIL PROTECTED] If you not only found the problem but solved it and generated a patch then e-mail it to [EMAIL PROTECTED] instead. Please use the command "diff -c" to generate the patch. You may also enter a bug report at http://www.postgresql.org/ instead of e-mail-ing this form. POSTGRESQL BUG REPORT TEMPLATE Your name :Christian Zagrodnick Your email address :[EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) :Intel Pentium Operating System (example: Linux 2.0.26 ELF) :Linux 2.4.18-686-smp PostgreSQL version (example: PostgreSQL-7.2.1): PostgreSQL-7.2.1 Compiler used (example: gcc 2.95.2) :gcc 2.95.4 (i think; i used the debian package) Please enter a FULL description of your problem: I built a plpgsql function that behaves strange. Starting a new session it works one time. Dumping the function in the same session again let it work as it is supposed to. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- create table mystifier ( rd int8 check (1::bool and 0::bool) -- table has to be empty ); create or replace function mystify(int8) returns varchar as ' DECLARE id ALIAS for $1; random int8; rec RECORD; newid varchar; qry varchar; BEGIN SELECT INTO rec * FROM mystifier; IF NOT FOUND THEN qry:=''SELECT (random()*5)::int8 as rd''; FOR rec IN EXECUTE qry LOOP random:=rec.rd; END LOOP; CREATE TEMP TABLE mystifier (rd int8); INSERT INTO mystifier VALUES (rec.rd); SELECT INTO rec * FROM mystifier; END IF; random:=rec.rd; newid:=to_char(id#random,''000''); RETURN newid; END; ' language 'plpgsql'; chzeamt=> select * from mystifier ; rd (0 rows) chzeamt=> select mystify(3); mystify -- 00147097331 (1 row) chzeamt=> select mystify(3); NOTICE: Error occurred while executing PL/pgSQL function mystify NOTICE: line 15 at SQL statement ERROR: Relation 'mystifier' already exists chzeamt=> create or replace function mystify(int8) returns varchar as ' . chzeamt=> select mystify(3); mystify -- 00147097331 (1 row) chzeamt=> select mystify(3); mystify -- 00147097331 (1 row) chzeamt=> select mystify(8); mystify -- 00147097336 (1 row) chzeamt=> select mystify(8); mystify -- 00147097336 (1 row) chzeamt=> \q zagy@lisa:~> psql -U chzeamt chzeamt chzeamt=> select mystify(46578); mystify -- 00287181363 (1 row) chzeamt=> select mystify(46578); NOTICE: Error occurred while executing PL/pgSQL function mystify NOTICE: line 15 at SQL statement ERROR: Relation 'mystifier' already exists chzeamt=> create or replace function mystify(int8) returns varchar as ' . chzeamt=> select mystify(46570); mystify -- 00287181355 (1 row) chzeamt=> select mystify(46570); mystify -- 00287181355 (1 row) ... and so on. The function might be stupid, but well... ;-) If you know how this problem might be fixed, list the solution below: - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Bug #679: Time input format is no longer ISO 8601 compliant
> ... it should be possible to enter 'time' fields as "10" for 10:00:00. Try prepending a "T" to the front of the string (also allowed per ISO-8601). Will be fixed in the next release to allow the number-only form. - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
