Plpgsql problem passing ROWTYPE to function

[Karl O. Pinc]

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_cyclest ats
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_cyclest ats(cycles.snam e%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.statda te%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(cycl es.sname%TYPE
, cycles
, cycles.sname%TY PE
, cycles.tdate%TY PE)
RETURNS INT
LANGUAGE plpgsql
SECURITY DEFINER
AS ' ...


------------------------------------------------------------------
BTW, if I write:

CREATE FUNCTION _makestats(cycl es.sname%TYPE
, cycles%ROWTYPE
, cycles.sname%TY PE
, cycles.tdate%TY PE)

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Re: Plpgsql problem passing ROWTYPE to function

"Karl O. Pinc" <kop@meme.com > writes:[color=blue]
> 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[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Re: Plpgsql problem passing ROWTYPE to function

"Karl O. Pinc" <kop@meme.com > writes:[color=blue]
> 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[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Karl O. Pinc]

Re: Plpgsql problem passing ROWTYPE to function


On 2004.04.29 17:58 Tom Lane wrote:[color=blue]
> "Karl O. Pinc" <kop@meme.com > writes:[color=green]
> > 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[/color]
>
> plpgsql doesn't support that. It might do so for 7.5, if anyone gets
> around to finishing the work I started.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Karl O. Pinc]

Re: Plpgsql problem passing ROWTYPE to function


On 2004.04.29 17:58 Tom Lane wrote:[color=blue]
> "Karl O. Pinc" <kop@meme.com > writes:[color=green]
> > 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[/color]
>
> plpgsql doesn't support that. It might do so for 7.5, if anyone gets
> around to finishing the work I started.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Re: Plpgsql problem passing ROWTYPE to function

"Karl O. Pinc" <kop@meme.com > writes:[color=blue]
> What a drag. The documentation says it supports this.[/color]

No it doesn't ...
[color=blue]
> "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."[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Re: Plpgsql problem passing ROWTYPE to function

"Karl O. Pinc" <kop@meme.com > writes:[color=blue]
> What a drag. The documentation says it supports this.[/color]

No it doesn't ...
[color=blue]
> "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."[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Karl O. Pinc]

Re: Plpgsql problem passing ROWTYPE to function


On 2004.04.29 22:21 Tom Lane wrote:
[color=blue][color=green]
> > "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[/color]
> $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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Karl O. Pinc]

Re: Plpgsql problem passing ROWTYPE to function


On 2004.04.29 22:21 Tom Lane wrote:
[color=blue][color=green]
> > "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[/color]
> $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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Chris Gamache]

Foreign Key ON DELETE CASCADE Performance

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 uniqueidentifie r
table2.p1 uniqueidentifie r
table3.p1 uniqueidentifie r
table4.p1 uniqueidentifie r
table4.q1 uniqueidentifie r
table5.q1 uniqueidentifie r
....
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..107 3.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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Chris Gamache]

Foreign Key ON DELETE CASCADE Performance

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 uniqueidentifie r
table2.p1 uniqueidentifie r
table3.p1 uniqueidentifie r
table4.p1 uniqueidentifie r
table4.q1 uniqueidentifie r
table5.q1 uniqueidentifie r
....
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..107 3.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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruno Wolff III]

Re: Foreign Key ON DELETE CASCADE Performance

On Fri, Apr 30, 2004 at 09:35:39 -0700,
Chris Gamache <cgg007@yahoo.c om> wrote:[color=blue]
>
> What can be done to increase the overall speed of this transaction, keeping the
> FKey Constraints in-place?[/color]

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bruno Wolff III]

Re: Foreign Key ON DELETE CASCADE Performance

On Fri, Apr 30, 2004 at 09:35:39 -0700,
Chris Gamache <cgg007@yahoo.c om> wrote:[color=blue]
>
> What can be done to increase the overall speed of this transaction, keeping the
> FKey Constraints in-place?[/color]

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Chris Gamache]

Re: Foreign Key ON DELETE CASCADE Performance

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:[color=blue]
> On Fri, Apr 30, 2004 at 09:35:39 -0700,
> Chris Gamache <cgg007@yahoo.c om> wrote:[color=green]
> >
> > What can be done to increase the overall speed of this transaction, keeping[/color]
> the[color=green]
> > FKey Constraints in-place?[/color]
>
> 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.[/color]






_______________ _______________ ____
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs

http://hotjobs.sweepstakes.yahoo.com/careermakeover

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Chris Gamache]

Re: Foreign Key ON DELETE CASCADE Performance

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:[color=blue]
> On Fri, Apr 30, 2004 at 09:35:39 -0700,
> Chris Gamache <cgg007@yahoo.c om> wrote:[color=green]
> >
> > What can be done to increase the overall speed of this transaction, keeping[/color]
> the[color=green]
> > FKey Constraints in-place?[/color]
>
> 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.[/color]






_______________ _______________ ____
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs

http://hotjobs.sweepstakes.yahoo.com/careermakeover

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org