%ROWTYPE in PL/PgSQL

Поиск
Список
Период
Сортировка
От Lane Stevens
Тема %ROWTYPE in PL/PgSQL
Дата
Msg-id 20020820150502.W1618@jacha.cycletime.com
обсуждение исходный текст
Список pgsql-general
I have cross-posted this to pgsql-bugs as it relates to a past thread
regarding documentation for PL/PgSQL and %ROWTYPE.

I need help calling functions that accept rows as parameters from plpgsql.  It
appears that I can invoke a function that expects a row as a parameter from
a SQL statement, but can't do so from another function.  See the following
example.

create table some_numbers (i int);
insert into some_numbers (i) values (1);
insert into some_numbers (i) values (-1);
create or replace function f3(some_numbers) returns text as '
begin
    if $1.i < 0 then
       return ''negative'';
    elsif $1.i > 0 then
       return ''positive'';
    else
       return ''zero'';
    end if;
end;
' language 'plpgsql';

db=# select f3(some_numbers) from some_numbers;
    f3
----------
 positive
 negative
(2 rows)

This appears to work (although not sure that the name of the parameter
is intuitive) correctly...

Now, I define another function, f4

create or replace function f4() returns text as '
declare
    n some_numbers%rowtype;
    t text;
    c cursor for select * from some_numbers;
begin

    t := '''';
    open c;
    loop
        fetch c into n;
        exit when not found;
        t := t || f3(n);
    end loop;
    return t;
end;
' language 'plpgsql';

db=# select f4();
NOTICE:  Error occurred while executing PL/pgSQL function f4
NOTICE:  line 12 at assignment
ERROR:  Attribute 'n' not found

What's up with this?

Some more counter examples that help narrow the problem to the
handling of rows in the calling function follow.

create or replace function f5() returns text as '
declare
    n some_numbers%rowtype;
    t text;
    c cursor for select * from some_numbers;
begin
    t := '''';
    open c;
    loop
        fetch c into n;
        exit when not found;
        if n.i < 0 then
           t := t || ''negative '';
        elsif n.i > 0 then
           t := t || ''positive '';
        else
           t := t || ''zero '';
        end if;
    end loop;
    return t;
end;
' language 'plpgsql';

create or replace function f6(int) returns text as '
begin
    if $1 < 0 then
       return ''negative '';
    elsif $1 > 0 then
       return ''positive '';
    else
       return ''zero '';
    end if;
end;
' language 'plpgsql';


create or replace function f7() returns text as '
declare
declare
    n some_numbers%rowtype;
    t text;
    c cursor for select * from some_numbers;
begin

    t := '''';
    open c;
    loop
        fetch c into n;
        exit when not found;
        t := t || f6(n.i);
    end loop;
    return t;
end;
' language 'plpgsql';


db=# select f5();
         f5
--------------------
 positive negative
(1 row)

db=# select f7();
         f7
--------------------
 positive negative
(1 row)


Thanks,
Lane.
--
Lane Stevens
Terrapin Technologies, Inc.
http://www.cycletime.com

В списке pgsql-general по дате отправления:

Предыдущее
От: elein
Дата:
Сообщение: Table Inheritance
Следующее
От: Clinton Adams
Дата:
Сообщение: Re: DAO for PostGRE