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 Test