%ROWTYPE as PL/pgsql argument

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