Обсуждение: %ROWTYPE as PL/pgsql argument
Can mytable%ROWTYPE be an argument to a function?
Just checking. I tried by got:
ERROR: parser: parse error at or near "%"
CREATE OR REPLACE FUNCTION testFunc(mytable%ROWTYPE)
RETURNS BOOLEAN AS'
RETURN TRUE:
END;
' LANGUAGE 'plpgsql' WITH (isstrict);
Richard
Richard Emberson <emberson@phc.net> writes:
> CREATE OR REPLACE FUNCTION testFunc(mytable%ROWTYPE)
There's no %ROWTYPE in Postgres SQL. There's no need for it, because
the table name is also the name of the rowtype datatype --- so you
should have written just
CREATE OR REPLACE FUNCTION testFunc(mytable)
regards, tom lane
Tom Lane wrote:
> Richard Emberson <emberson@phc.net> writes:
> > CREATE OR REPLACE FUNCTION testFunc(mytable%ROWTYPE)
>
> There's no %ROWTYPE in Postgres SQL. There's no need for it, because
> the table name is also the name of the rowtype datatype --- so you
> should have written just
>
> CREATE OR REPLACE FUNCTION testFunc(mytable)
>
> regards, tom lane
If I try the following, I get the error:
=> select x(1);
NOTICE: Error occurred while executing PL/pgSQL function x
NOTICE: line 9 at return
ERROR: Attribute 'type_row_v' not found
So how do I generate a row that can be used as a parameter to a
function?
thanks
CREATE OR REPLACE FUNCTION x(
BIGINT
)
RETURNS BIGINT AS '
DECLARE
type_id_p ALIAS FOR $1;
type_row_v type%ROWTYPE;
BEGIN
SELECT * INTO type_row_v FROM type
WHERE type_id = type_id_p;
RETURN xy(type_row_v);
END;
' LANGUAGE 'plpgsql' WITH (isstrict);
CREATE OR REPLACE FUNCTION xy(
type
)
RETURNS BIGINT AS '
DECLARE
type_row_p ALIAS FOR $1;
BEGIN
IF type_row_p.type_id IS NULL THEN
RETURN -2;
END IF;
RETURN type_row_p.type_kind;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);
Richard
Tom Lane wrote:
> Richard Emberson <emberson@phc.net> writes:
> > CREATE OR REPLACE FUNCTION testFunc(mytable%ROWTYPE)
>
> There's no %ROWTYPE in Postgres SQL. There's no need for it, because
> the table name is also the name of the rowtype datatype --- so you
> should have written just
>
> CREATE OR REPLACE FUNCTION testFunc(mytable)
>
> regards, tom lane
The following does work ... (does the refcursor get closed automatically
in this example?)
CREATE OR REPLACE FUNCTION x(
BIGINT
)
RETURNS BIGINT AS '
DECLARE
type_id_p ALIAS FOR $1;
type_rc_v REFCURSOR;
BEGIN
OPEN type_rc_v FOR SELECT * FROM type
WHERE type_id = type_id_p;
RETURN xxx(type_rc_v);
END;
' LANGUAGE 'plpgsql' WITH (isstrict);
CREATE OR REPLACE FUNCTION xxx(
REFCURSOR
)
RETURNS BIGINT AS '
DECLARE
type_rc_p ALIAS FOR $1;
type_row_v type%ROWTYPE;
BEGIN
FETCH type_rc_p INTO type_row_v;
IF type_row_v.type_id IS NULL THEN
RETURN -2;
END IF;
RETURN type_row_v.type_kind;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);
Richard Emberson wrote:
> Tom Lane wrote:
>
> > Richard Emberson <emberson@phc.net> writes:
> > > CREATE OR REPLACE FUNCTION testFunc(mytable%ROWTYPE)
> >
> > There's no %ROWTYPE in Postgres SQL. There's no need for it, because
> > the table name is also the name of the rowtype datatype --- so you
> > should have written just
> >
> > CREATE OR REPLACE FUNCTION testFunc(mytable)
> >
> > regards, tom lane
>
> If I try the following, I get the error:
> => select x(1);
> NOTICE: Error occurred while executing PL/pgSQL function x
> NOTICE: line 9 at return
> ERROR: Attribute 'type_row_v' not found
>
> So how do I generate a row that can be used as a parameter to a
> function?
> thanks
At the time beeing you can't do that with PL/pgSQL. The
language can receive rows as parameters, but it cannot build
those rows and pass them down to called functions.
Jan
>
>
> CREATE OR REPLACE FUNCTION x(
> BIGINT
> )
> RETURNS BIGINT AS '
> DECLARE
> type_id_p ALIAS FOR $1;
> type_row_v type%ROWTYPE;
> BEGIN
> SELECT * INTO type_row_v FROM type
> WHERE type_id = type_id_p;
>
> RETURN xy(type_row_v);
> END;
> ' LANGUAGE 'plpgsql' WITH (isstrict);
>
> CREATE OR REPLACE FUNCTION xy(
> type
> )
> RETURNS BIGINT AS '
> DECLARE
> type_row_p ALIAS FOR $1;
> BEGIN
>
> IF type_row_p.type_id IS NULL THEN
> RETURN -2;
> END IF;
>
> RETURN type_row_p.type_kind;
> END;
> ' LANGUAGE 'plpgsql' WITH (isstrict);
>
>
> Richard
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
#======================================================================#
# 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:
> At the time beeing you can't do that with PL/pgSQL. The
> language can receive rows as parameters, but it cannot build
> those rows and pass them down to called functions.
Hmm, doesn't it work to do
declare var some-row-type;
select x, y, z into var;
select otherfunc(var);
regards, tom lane
Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> > At the time beeing you can't do that with PL/pgSQL. The
> > language can receive rows as parameters, but it cannot build
> > those rows and pass them down to called functions.
>
> Hmm, doesn't it work to do
>
> declare var some-row-type;
>
> select x, y, z into var;
>
> select otherfunc(var);
>
> regards, tom lane
I tried it and it did not work. It gave an error message something like
"var attribute not found" at the point in the procedure where the
otherfunc was called.
In the following it died at the line:
RETURN xy(type_row_v);
(by the way, if you change the line:
type_row_v type%ROWTYPE;
to
type_row_v type;
the connection with the backend is cut.)
CREATE OR REPLACE FUNCTION x(
BIGINT
)
RETURNS BIGINT AS '
DECLARE
type_id_p ALIAS FOR $1;
type_row_v type%ROWTYPE;
BEGIN
SELECT * INTO type_row_v FROM type
WHERE type_id = type_id_p;
RETURN xy(type_row_v);
END;
' LANGUAGE 'plpgsql' WITH (isstrict);
CREATE OR REPLACE FUNCTION xy(
type
)
RETURNS BIGINT AS '
DECLARE
type_row_p ALIAS FOR $1;
BEGIN
IF type_row_p.type_id IS NULL THEN
RETURN -2;
END IF;
RETURN type_row_p.type_kind;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);
Richard
Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> > At the time beeing you can't do that with PL/pgSQL. The
> > language can receive rows as parameters, but it cannot build
> > those rows and pass them down to called functions.
>
> Hmm, doesn't it work to do
>
> declare var some-row-type;
>
> select x, y, z into var;
>
> select otherfunc(var);
Yes,
it doesn't work. Actually, giving just a relname as 'some-
row-type' crashes the backend. Will take a look at it
tomorrow.
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:
> it doesn't work. Actually, giving just a relname as 'some-
> row-type' crashes the backend. Will take a look at it
> tomorrow.
I already did. It looks like plpgsql is mistakenly treating the type
as a scalar type (eg, using oidin/oidout to convert it ... which after
all is what pg_type says to do). The callee then crashes because it's
expecting a pointer to a tuple, and isn't getting one. Probably the
right fix is to implicitly assume %ROWTYPE behavior if we see that a
variable's type is marked typtype = 'c' in pg_type.
Meanwhile, passing a rowtype variable to a function doesn't work either.
The initial problem is that the reference to the rowtype variable never
gets replaced by an expression parameter reference, because
read_sql_construct() doesn't do anything with T_RECORD or T_ROW items;
I dunno if there are more problems beyond that one. (There are a lot
of other places that treat T_VARIABLE but not T_RECORD or T_ROW, too;
probably all of them need to be looked at.)
regards, tom lane