Thread: plpgsql and rowtypes
Happy New Year's day, and such!
Has anyone had any luck getting rowtyped declarations in plpgsql
to function? In particular, the documentation says:
23.3.2. Rowtypes
/name/ /table-datatype/;
A variable declared with a composite type (referenced by the name of the
table that defines that type) is called a / row/ variable. Such a
variable can hold a whole row of a SELECT or FOR query result, so long
as that query's column set matches the declared rowtype of the variable.
The individual fields of the row value are accessed using the usual dot
notation, for example rowvar.field.
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 .
Only the user-defined attributes of a table row are accessible in a
rowtype variable, not OID or other system attributes (because the row
could be from a view). The fields of the rowtype inherit the table's
field size or precision for data types such as char(n).
However, unless I am misunderstanding something silly, I get quite a
different result when I try this:
test=# create table testtable(a int, b int);
CREATE
test=# create function test() returns int as '
test'# DECLARE
test'# tt testtable;
test'# BEGIN
test'# tt.a := 4;
test'# RETURN tt.a;
test'# END;
test'# ' language 'plpgsql';
CREATE
test=# select test();
NOTICE: plpgsql: ERROR during compile of test near line 2
ERROR: parse error at or near "testtable"
Any ideas? I'm running 7.1.3...
Thanks,
dj trombley
<dtrom@bumba.net>
Dave Trombley <dtrom@bumba.net> writes:
> test'# DECLARE
> test'# tt testtable;
Try
DECLARE
tt testtable%ROWTYPE;
Not sure why plpgsql insists on the explicit marker that a rowtype is
meant, but it does.
I'll fix the documentation to explain this correctly.
regards, tom lane
Tom Lane wrote:
>
>Not sure why plpgsql insists on the explicit marker that a rowtype is
>meant, but it does.
>
Oh, spiffy. This works now, thanks.
One other thing that seems odd to me follows, the relevant
documentation is section 23.6.2.4. Again, I could be misunderstanding
the semantics, but it seems intuitively wrong that this construct should
behave as I'm seeing:
test=# CREATE FUNCTION ifelsetest(int) RETURNS bool AS '
test'# BEGIN
test'# IF $1 = 4 THEN RETURN true;
test'# ELSIF $1 = 7 THEN RETURN true;
test'# ELSE return false;
test'# END IF;
test'# END; ' language 'plpgsql';
CREATE
test=# select ifelsetest(2);
ifelsetest
------------
f
(1 row)
test=# select ifelsetest(4);
ifelsetest
------------
t
(1 row)
test=# select ifelsetest(7);
ifelsetest
------------
f
(1 row)
Cheers,
dj trombley
<dtrom@bumba.net>
Dave Trombley <dtrom@bumba.net> writes:
> One other thing that seems odd to me follows, the relevant
> documentation is section 23.6.2.4.
What version are you using? ELSIF support didn't exist in 7.1,
but it seems to work for me with CVS tip.
regards, tom lane
Tom Lane wrote:
>
>What version are you using? ELSIF support didn't exist in 7.1,
>but it seems to work for me with CVS tip.
>
I'm using the current release version (7.1.3), downloaded and
compiled it today after having this problem. I'm going to try it again
on a fresh box, in case something funcky is going on with library
versions, etc.
-dj
Dave Trombley <dtrom@bumba.net> writes:
> Tom Lane wrote:
>> What version are you using? ELSIF support didn't exist in 7.1,
>> but it seems to work for me with CVS tip.
>>
> I'm using the current release version (7.1.3), downloaded and
> compiled it today after having this problem. I'm going to try it again
> on a fresh box, in case something funcky is going on with library
> versions, etc.
No, I think you miss my point: 7.1.* doesn't have ELSIF support in
plpgsql; that's a new feature for 7.2. You must be reading the 7.2
docs and trying to apply what they say to 7.1.*. That'll often work
but not always ...
regards, tom lane
Dave Trombley wrote:
> I'm using the current release version (7.1.3), downloaded and
> compiled it today after having this problem. I'm going to try it
> again on a fresh box, in case something funcky is going on with
> library versions, etc.
>
Ok, yeah, now I'm pretty sure I'm not insane. Well, maybe not /so/
sure. =) But, I've messed up pl_exec.c to dump some information about
the branches it's taking in an IF statement. It seems that the false
body part in the PLpgSQL_stmt_if structure is not being set correctly in
a IF-THEN-ELSIF-ELSE (same example as before):
test=# select itet(7);
Evaluating condition (SELECT $1 = 4)...
Exec'ing false part...
False part statements: 64 allocated, 1 actually used.
False part statement 0: cmdtype 9, line 4
itet
------
f
(1 row)
The command type is correct for that line, but the line number
itself is incorrect, line 4 is the ELSE part, not the ELSIF. Looking at
the version of the source I have, the grammar seems to be old, version
1.17 of gram.y. The CVS log tells me that this is exactly one version
before ELSIF support was added, but it seems oddball that I wouldn't be
griped at for putting some random words into my function (namely ELSIF).
I'd persue it further, but frankly yacc/bison tends to give me
migraines, and I'm still reeling somewhat from the new year's party... =)
Any idea why I have this older version? I don't know when the
releases were made, but the same file is in 7.1.2 and 7.1.1. 7.1 has a
single minor revision earlier. (Or is the documentation just ahead of
the releases?) I guess I'll just go grab the CVS sources and mess
around with those for now.
Cheers,
dj trombley
<dtrom@bumba.net>
Dave Trombley <dtrom@bumba.net> writes:
> ... but it seems oddball that I wouldn't be
> griped at for putting some random words into my function (namely ELSIF).
A fair complaint, but the way plpgsql currently works is that any-random-
sentence-whatever is parsed as "unspecified SQL query". When and if
executed, it'll be fed down to the main SQL parser, which will spit up
on the unknown query keyword. But the way your function was formulated,
control could never get there. What plpgsql saw was
IF condition THEN
RETURN expr;
unspecified-SQL-query;
ELSE
something-else;
ENDIF;
and of course control never got past the RETURN to find out that the
unspecified-SQL-query was bogus.
This is not to suggest that the present behavior is adequate. plpgsql's
parser needs to be redesigned from the ground up so that it can detect
elementary syntax errors sooner. But I fear it's not a simple fix :-(
One possible improvement is for plpgsql's parser to feed
unspecified-SQL-queries to the main grammar (and no further)
during its syntax pass. Jan, any thoughts?
regards, tom lane
Dave Trombley wrote:
> Dave Trombley wrote:
>
> > I'm using the current release version (7.1.3), downloaded and
> > compiled it today after having this problem. I'm going to try it
> > again on a fresh box, in case something funcky is going on with
> > library versions, etc.
> >
> Ok, yeah, now I'm pretty sure I'm not insane. Well, maybe not /so/
> sure. =) But, I've messed up pl_exec.c to dump some information about
> the branches it's taking in an IF statement. It seems that the false
> body part in the PLpgSQL_stmt_if structure is not being set correctly in
> a IF-THEN-ELSIF-ELSE (same example as before):
Sometimes I wonder why nobody uses
#option dump
That in the first line of the procedure body causes the
backend to produce alot of useful information in the
postmaster log.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Jan Wieck <janwieck@yahoo.com> writes:
> Sometimes I wonder why nobody uses
> #option dump
Probably because it's utterly undocumented. Care to fix that?
regards, tom lane
Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> > Sometimes I wonder why nobody uses
> > #option dump
>
> Probably because it's utterly undocumented. Care to fix that?
Hmmm,
good point - I mean, sounds reasonable. Looks like I should
care to.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Tom Lane wrote:
>
>No, I think you miss my point: 7.1.* doesn't have ELSIF support in
>plpgsql; that's a new feature for 7.2. You must be reading the 7.2
>docs and trying to apply what they say to 7.1.*. That'll often work
>but not always ...
>
Ah, yep. That's exactly what I was doing, apologies. I saw the
"Current or Development Docs" link on the front page, and was thinking
those were the current release docs, despite the fact that it says '7.2'
several times on the page that points to.
Thanks,
-dj