473,746 Members | 2,475 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_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 <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
14 5831
"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
"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

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

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

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

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

Nov 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2475
by: Rajesh Kumar Mallah | last post by:
Hi, profile_row profile_master%ROWTYPE; in a plpgsql function gives the error below tradein_clients=# SELECT general.create_accounts(); WARNING: plpgsql: ERROR during compile of create_accounts near line 8
3
1333
by: Uwe C. Schroeder | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, following odity: I have a table "quote". In a stored proc I do a
4
4968
by: Bill Moran | last post by:
I've got a bit of a strange problem that's causing me some MAJOR headaches. I'm developing the server-side of a large database application in PostgreSQL. This consists of a C daemon, and a LOT of stored functions in the database. I'm developing this in conjunction with another company, who is developing the the client side. I've got a 7.4 server that I'm developing on, and once a day I push my changes up to a common
5
1611
by: Thomas Chille | last post by:
Hi, i am playing around with PLpgSQL and can not solve one problem: I am fetching some rows of a special rowtype and wanna give this rows step by step to a function with this rowtype as parameter. evertime i get the errormessage: ERROR: Attribut »_row« not found but the Attribut exists and has values in his fields. i have absolutly no idea was happens there. can someone help me please? thanx
0
534
by: Karl O. Pinc | last post by:
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
1
4665
by: Thomas Schoen | last post by:
Hi, is it possible to use a parameter of a plpgsql-function to order a selection inside the function? What i would like to do is pass a column-name/alias-name to a plpgsql function and use the variable as order-by-parameter like this: create function foo(varchar) RETURNS SETOF test AS '
2
1859
by: David Boone | last post by:
I've been trying to create functions with postgres, but it seems that queries run within a function take wayyy too long to complete. The increased time seems to be in the actual queries, not function call overhead or something, but I can't for the life of me figure out why it's slower like this. I've simplified it to what you see below. Any insight would be *much* appreciated. Thanks! - Dave
2
3820
by: Mark Cave-Ayland | last post by:
Hi everyone, I'm trying to write a recursive plpgsql function in PostgreSQL 7.4.2 that given a tree node id (ictid) will return all the nodes below it in the tree, one row per node. When I try and execute the function I get the following error message: CONTEXT: PL/pgSQL function "findsubcategories" line 15 at for over select rows PL/pgSQL function "findsubcategories" line 15 at for over select rows
9
10915
by: Karl O. Pinc | last post by:
I want to return multiple values, but not a set, only a single row, from a plpgsql function and I can't seem to get it to work. (I suppose I'd be happy to return a set, but I can't seem to make that work either. Anyway, what's wrong with this?) Version is: $ rpm -q postgresql
0
8974
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8800
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9500
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9349
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9285
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9218
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8227
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4575
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
2199
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.