By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Plpgsql problem passing ROWTYPE to function

P: n/a
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 <ko*@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

Nov 23 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
"Karl O. Pinc" <ko*@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

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

http://archives.postgresql.org

Nov 23 '05 #2

P: n/a
"Karl O. Pinc" <ko*@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

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

http://archives.postgresql.org

Nov 23 '05 #3

P: n/a

On 2004.04.29 17:58 Tom Lane wrote:
"Karl O. Pinc" <ko*@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/s...ATION-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 <ko*@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

Nov 23 '05 #4

P: n/a

On 2004.04.29 17:58 Tom Lane wrote:
"Karl O. Pinc" <ko*@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/s...ATION-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 <ko*@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

Nov 23 '05 #5

P: n/a
"Karl O. Pinc" <ko*@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

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

http://archives.postgresql.org

Nov 23 '05 #6

P: n/a
"Karl O. Pinc" <ko*@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

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

http://archives.postgresql.org

Nov 23 '05 #7

P: n/a

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 <ko*@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

Nov 23 '05 #8

P: n/a

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 <ko*@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

Nov 23 '05 #9

P: n/a
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

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

Nov 23 '05 #10

P: n/a
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

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

Nov 23 '05 #11

P: n/a
On Fri, Apr 30, 2004 at 09:35:39 -0700,
Chris Gamache <cg****@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.

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

http://archives.postgresql.org

Nov 23 '05 #12

P: n/a
On Fri, Apr 30, 2004 at 09:35:39 -0700,
Chris Gamache <cg****@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.

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

http://archives.postgresql.org

Nov 23 '05 #13

P: n/a
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 <br***@wolff.to> wrote:
On Fri, Apr 30, 2004 at 09:35:39 -0700,
Chris Gamache <cg****@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

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

http://archives.postgresql.org

Nov 23 '05 #14

P: n/a
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 <br***@wolff.to> wrote:
On Fri, Apr 30, 2004 at 09:35:39 -0700,
Chris Gamache <cg****@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

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

http://archives.postgresql.org

Nov 23 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.