Thread: Plpgsql problem passing ROWTYPE to function
Hi,
Thought perhaps some other eyes than mine can tell if I'm doing
something wrong here or if there's a bug somewhere. I've never
passed a ROWTYPE varaible to a function but I don't see where
the problem is.
I keep getting errors like (the first is my debug output):
NOTICE: last cycle is: 11
WARNING: Error occurred while executing PL/pgSQL function
rebuild_cyclestats
WARNING: line 69 at assignment
ERROR: Attribute "last_cycle" not found
(Line number is off as I've snipped out code to post here.)
$ psql --version
psql (PostgreSQL) 7.3.4
$ rpm -q postgresql
postgresql-7.3.4-3.rhl9
$ cat /etc/redhat-release Red Hat Linux release 9 (Shrike)
(I don't know how to query the server for it's version number,
I assume it's in sync with the client.)
-------------------------------------------------
CREATE FUNCTION rebuild_cyclestats(cycles.sname%TYPE)
RETURNS INT
LANGUAGE plpgsql
SECURITY DEFINER
AS '
DECLARE
this_sname ALIAS for $1;
last_cycle cycles%ROWTYPE;
this_cycle cycles%ROWTYPE;
this_statdate biograph.statdate%TYPE;
BEGIN
SELECT INTO last_cycle * FROM cycles WHERE sname = this_sname
AND seq = 1;
IF NOT FOUND THEN
-- There are no cycles. Do nothing.
RETURN 0;
END IF;
-- debug
raise notice ''last cycle is: %'', last_cycle.cid;
-- The female''s last cycle continues to her statdate.
SELECT INTO this_statdate statdate FROM biograph WHERE sname =
this_sname;
--error is here
PERFORM _makestats(this_sname
, last_cycle
, NULL
, this_statdate);
RETURN 0;
END;
';
CREATE FUNCTION _makestats(cycles.sname%TYPE
, cycles
, cycles.sname%TYPE
, cycles.tdate%TYPE)
RETURNS INT
LANGUAGE plpgsql
SECURITY DEFINER
AS ' ...
------------------------------------------------------------------
BTW, if I write:
CREATE FUNCTION _makestats(cycles.sname%TYPE
, cycles%ROWTYPE
, cycles.sname%TYPE
, cycles.tdate%TYPE)
I always get:
ERROR: parser: parse error at or near "%" at character 81
Thanks.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
"Karl O. Pinc" <kop@meme.com> writes:
> Thought perhaps some other eyes than mine can tell if I'm doing
> something wrong here or if there's a bug somewhere. I've never
> passed a ROWTYPE varaible to a function
plpgsql doesn't support that. It might do so for 7.5, if anyone gets
around to finishing the work I started.
regards, tom lane
On 2004.04.29 17:58 Tom Lane wrote: > "Karl O. Pinc" <kop@meme.com> writes: > > Thought perhaps some other eyes than mine can tell if I'm doing > > something wrong here or if there's a bug somewhere. I've never > > passed a ROWTYPE varaible to a function > > plpgsql doesn't support that. It might do so for 7.5, if anyone gets > around to finishing the work I started. What a drag. The documentation says it supports this. Is this a bug in the documentation or something I'm not understanding? See: http://www.postgresql.org/docs/7.4/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES "Parameters to a function can be composite types (complete table rows). In that case, the corresponding identifier $n will be a row variable, and fields can be selected from it, for example $1.user_id." Thanks for the help. Karl <kop@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein
"Karl O. Pinc" <kop@meme.com> writes:
> What a drag. The documentation says it supports this.
No it doesn't ...
> "Parameters to a function can be composite types (complete table
> rows). In that case, the corresponding identifier $n will be a row
> variable, and fields can be selected from it, for example $1.user_id."
That says that a parameter passed *into* a plpgsql function can be a
rowtype, not that plpgsql supports doing something with the whole-row
variable in function calls it makes.
I agree it's a drag :-(
regards, tom lane
On 2004.04.29 22:21 Tom Lane wrote:
> > "Parameters to a function can be composite types (complete table
> > rows). In that case, the corresponding identifier $n will be a row
> > variable, and fields can be selected from it, for example
> $1.user_id."
>
> That says that a parameter passed *into* a plpgsql function can be a
> rowtype, not that plpgsql supports doing something with the whole-row
> variable in function calls it makes.
I'm afriad I don't understand.
I don't want to have a plpgsql function write a plpgsql function,
all I want to do is have a plpgsql function call a plpgsql function,
pass it a ROWTYPE variable and read the 'column' values of
the ROWTYPE variable in the called function. What use is it to be able
to write
a plpgsql function that receives a ROWTYPE variable if you can't
call the function from plpgsql?
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
PostgreSQL 7.4.2 ... The tables in question have been vacuumed.
...stepping gingerly into the woods of foreign keys... I need some advice:
Given a foriegn key structure:
table1.p1 uniqueidentifier
table2.p1 uniqueidentifier
table3.p1 uniqueidentifier
table4.p1 uniqueidentifier
table4.q1 uniqueidentifier
table5.q1 uniqueidentifier
...
CONSTRAINT table2_p1_fkey FOREIGN KEY (p1) REFERENCES table1 (p1) ON UPDATE
CASCADE ON DELETE CASCADE;
CONSTRAINT table3_p1_fkey FOREIGN KEY (p1) REFERENCES table1 (p1) ON UPDATE
CASCADE ON DELETE CASCADE;
CONSTRAINT table4_p1_fkey FOREIGN KEY (p1) REFERENCES table1 (p1) ON UPDATE
CASCADE ON DELETE CASCADE;
CONSTRAINT table5_q1_fkey FOREIGN KEY (q1) REFERENCES table4 (q1) ON UPDATE
CASCADE ON DELETE CASCADE;
I want to clean every one of those tables out with a "delete from table1;" ...
So, If I :
db=# explain delete from table1;
I get something like this:
QUERY PLAN
------------------------------------------------------------------
Seq Scan on table1 (cost=0.00..1073.80 rows=39780 width=6)
(1 row)
It would appear that the query would run as fast as the table could be scanned.
But the query takes so long, I've never let it finish! Of course, it is because
it has to cascade the delete... I never dreamed it would be so expensive.
I can improve my performance within the transaction by using INITIALLY DEFERRED
vs. INITIALLY IMMEDIATE, but all that heavy lifting is just put off until
COMMIT.
What can be done to increase the overall speed of this transaction, keeping the
FKey Constraints in-place?
CG
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover
On Fri, Apr 30, 2004 at 09:35:39 -0700, Chris Gamache <cgg007@yahoo.com> wrote: > > What can be done to increase the overall speed of this transaction, keeping the > FKey Constraints in-place? The fastest thing to do if you really want to get rid of everything is do delete from for each of the tables starting with the ones that don't have any tables dependent on them. Another thing to note is that postgres doesn't automatically create an index on columns that reference another table. When you are taking advantage of cascading deletes you normally will want an index on such columns. If you insist upon being able to do the delete using just a delete from table1 you will want to create these indexes.
Bruno, you're a genius! :) I /had/ missed adding a single index. It made all the difference in the world. Boy! That's a relief. CG --- Bruno Wolff III <bruno@wolff.to> wrote: > On Fri, Apr 30, 2004 at 09:35:39 -0700, > Chris Gamache <cgg007@yahoo.com> wrote: > > > > What can be done to increase the overall speed of this transaction, keeping > the > > FKey Constraints in-place? > > The fastest thing to do if you really want to get rid of everything is > do delete from for each of the tables starting with the ones that don't > have any tables dependent on them. > > Another thing to note is that postgres doesn't automatically create > an index on columns that reference another table. When you are taking > advantage of cascading deletes you normally will want an index on such > columns. If you insist upon being able to do the delete using just a delete > from table1 you will want to create these indexes. __________________________________ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover