PL/pgSQL - parametr funkce typu record
Pavel Stehule
stehule na kix.fsv.cvut.cz
Úterý Duben 15 09:39:09 CEST 2003
zkusmo, mimo pouziti v trigru mi funguje
drop table omega;
create table omega(rc VARCHAR(10), jmeno VARCHAR(20));
create or replace function test2(omega) returns int as '
begin
raise notice ''%'', $1.rc;
return 1;
end;
' language plpgsql;
create or replace function testt1() returns setof omega as '
declare ome omega%ROWTYPE;
begin
ome.rc = ''kuku'';
return next ome;
return;
end' language plpgsql;
select test2(testu());
Pretypovat NEW na omega%ROWTYPE se mi nepodarilo, takze jedina moznost je,
pro kazdou polozku v recordu provest prirazeni z NEW do "lokalni" promenne
typu ROWTYPE
In article <Pine.LNX.3.96.1030415030113.752A-100000 na u24.u24>, Jiri Borik wrote:
> Prijemny den,
>
> narazil jsem na problem, jak zavolat z funkce jinou funkci a pri tom
> predat parametr typu record. Nejak se mi to nedari zapsat bez chyby (viz
> prilozeny kod) a v dokumentaci jsem nevycetl vic nez: Parameters to a
> function can be composite types (complete table rows).
>
> Jak se to dela ?
>
> Predem diky za radu.
>
> Jirka Borik <jiri.borik na volny.cz>
>
> ------------------------------------------------------------------------
> drop table tab_a;
> DROP
> create table tab_a (pk text primary key);
> psql:test3.sql:2: NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'tab_a_pkey' for table 'tab_a'
> CREATE
> ------------------------------------------------------------------------
> create function f1(tab_a) returns text as '
> declare
> t alias for $1;
> begin
> -- zkusime zmenit hodnotu, projevi se to v NEW ?
> t.pk := ''c'';
> return t.pk;
> end;
> ' language 'plpgsql';
> CREATE
> ------------------------------------------------------------------------
> create function f2(tab_a) returns text as '
> declare
> t alias for $1;
> begin
> return f1(t);
> end;
> ' language 'plpgsql';
> CREATE
> ------------------------------------------------------------------------
> drop function tr1();
> DROP
> create function tr1() returns opaque as '
> declare
> r text;
> begin
> r := f1(NEW);
> raise notice ''ret: %/%'', r, NEW.pk;
> return NEW;
> end;
> ' language 'plpgsql';
> CREATE
> create trigger a_upd before update on tab_a for each row execute procedure tr1();
> CREATE
> ------------------------------------------------------------------------
> insert into tab_a values('a');
> INSERT 657665 1
>
> update tab_a set pk='b';
> psql:test3.sql:37: ERROR: NEW used in non-rule query
>
> select pk,f1(tab_a) from tab_a;
> pk | f1
> ----+----
> a | c
> (1 row)
>
> select pk,f2(tab_a) from tab_a;
> psql:test3.sql:40: ERROR: Attribute 't' not found
> ------------------------------------------------------------------------
>
>
Další informace o konferenci Databases