Re: [SQL] problem with function in plpgsql

>>>>> "EB" == Eric BASIER <basier(at)ipgp(dot)jussieu(dot)fr> writes:

EB> Hello;
EB> I have a table pz like this :
EB> Table = pz
EB> +----------------------------------+----------------------------------+-------+

. . .

EB> I have to create a function who take me one row like that
EB> create function test_exist_pz (text,int4,int4,float,float) returns text
EB> as '
EB> declare
EB> pzrec pz%RowType;
EB> begin
EB> select * into pzrec
EB> from pz
EB> where typ = $1
EB> and in_unit = $2
EB> and out_unit = $3
EB> and A0 = $4
EB> and AF = $5;
EB> if not found then
EB> pzrec.cle = -1;
EB> end if;
EB> return pzrec;
EB> end;
EB> ' language 'plpgsql';
EB> CREATE
EB> When I try to work with the function it doesn't I have this result :
EB> seed=> select test_exist_pz('a',1,1,1.1,1.2);
EB> ERROR: attribute 'pzrec' not found
EB> I am not very familiar with plpgsql and so if there is somebody
EB> who can help me or if thre is somebody who can say where can
EB> I find documentation about plpgsql it is very well

There is at least one error in your text: function test_exist_pz
returns 'text' type, but you write 'return pzrec', value of
pz%RowType. If you need know - exist or no any row you can retype function:

create function test_exist_pz (text,int4,int4,float,float) returns bool
as '
declare
c int;
begin

select count(*) into pzrec
from pz
where typ = $1
and in_unit = $2
and out_unit = $3
and A0 = $4
and AF = $5;

return (c > 0);
end;
' language 'plpgsql';

But this function you can also code in 'sql' but no 'plpgsql' language.

--
Anatoly K. Lasareff Email: tolik(at)icomm(dot)ru
Senior programmer