can function arguments have the type tablename.columnname%TYPE?
Hi people,
I'm writing a plpgsql function in pgadminIII and want to know if the arguments can have the following type:
tablename.columnname%TYPE
If so, how do I accomplish this in pgadminIII as it doesn't allow me to change the arguments to this type.
The function I'm writing looks like this:
CREATE OR REPLACE FUNCTION totnrchange(a table1.resnr%TYPE, b table1.fnr%TYPE, c table1.fdate%TYPE, d table1.dep%TYPE, e table1.arr%TYPE, f table1.nrdays%TYPE)
RETURNS integer AS
$BODY$DECLARE
tot integer;
BEGIN
select count(resnr) into tot from table1 x where x.resnr=a and x.fnr=b and x.fdate=c and x.dep=d and x.arr=e and x.nrdays>=f group by x.resnr,x.fnr,x.fdate,x.dep,x.arr;
return tot;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Brgds
Dino
Le 11/05/2010 10:28, Dino Vliet a �crit :
[...]
I'm writing a plpgsql function in pgadminIII and want to know if the arguments can have the following type:tablename.columnname%TYPE
No, this construct is only available *inside* a PL/pgsql function body.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
Guillaume Lelarge <guillaume@lelarge.info> writes:
Le 11/05/2010 10:28, Dino Vliet a �crit :
I'm writing a plpgsql function in pgadminIII and want to know if the arguments can have the following type:
tablename.columnname%TYPE
No, this construct is only available *inside* a PL/pgsql function body.
That's incorrect: you can use it in a function declaration too.
Not sure what the OP's problem is exactly --- it might be specific to
pgAdmin.
regards, tom lane
Le 11/05/2010 16:16, Tom Lane a �crit :
Guillaume Lelarge <guillaume@lelarge.info> writes:
Le 11/05/2010 10:28, Dino Vliet a �crit :
I'm writing a plpgsql function in pgadminIII and want to know if the arguments can have the following type:
tablename.columnname%TYPE
No, this construct is only available *inside* a PL/pgsql function body.
That's incorrect: you can use it in a function declaration too.
Oh sure. With body, I meant the part between the two $$. But you're
right, I should have said "inside the declare and body parts of a
PL/pgsql function".
Not sure what the OP's problem is exactly --- it might be specific to
pgAdmin.
IIUC, he means doing something like this:
CREATE FUNCTION f1(arg1 table.column%TYPE ...
Which, AFAICT, is not possible. And you can't do that with pgAdmin, but
you can use the %TYPE in the declare and body parts in pgAdmin.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
Hi,
What is the main goal? Aren't you looking for a solution to simplify
arguments declaration?
If so then what about using this one:
CREATE OR REPLACE FUNCTION totnrchange(t table1)
RETURNS integer AS
$BODY$
DECLARE
tot integer;
BEGIN
select count(resnr) into tot from table1
where resnr = t.resnr and fnr = t.fnr ...
...
--
Sergey Konoplev
Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: Tuesday, May 11, 2010 11:56 AM
To: Tom Lane
Cc: Dino Vliet; pgsql-general@postgresql.org
Subject: Re: can function arguments have the type
tablename.columnname%TYPE?Le 11/05/2010 16:16, Tom Lane a écrit :
Guillaume Lelarge <guillaume@lelarge.info> writes:
Le 11/05/2010 10:28, Dino Vliet a écrit :
I'm writing a plpgsql function in pgadminIII and want to
know if the arguments can have the following type:
tablename.columnname%TYPE
No, this construct is only available *inside* a PL/pgsql function
bodyThat's incorrect: you can use it in a function declaration too.
Oh sure. With body, I meant the part between the two $$. But
you're right, I should have said "inside the declare and body
parts of a PL/pgsql function".Not sure what the OP's problem is exactly --- it might be
specific to
pgAdmin.
IIUC, he means doing something like this:
CREATE FUNCTION f1(arg1 table.column%TYPE ...
Which, AFAICT, is not possible. And you can't do that with
pgAdmin, but you can use the %TYPE in the declare and body
parts in pgAdmin.--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Guillaume,
I'm not sure why are you instisting that:
CREATE FUNCTION f1(arg1 table.column%TYPE ...
Which, AFAICT, is not possible.
It is definetily possible, i.e:
CREATE OR REPLACE FUNCTION myMaxCycle(i_n_Cell_id GP_CELL.cell_id%TYPE)
RETURNS GP_CYCLE.cycle_date_time%TYPE
AS $$...
Works in my 8.2 PG.
Regards,
Igor Neyman
Le 12/05/2010 16:56, Igor Neyman a �crit :
[...]
I'm not sure why are you instisting that:CREATE FUNCTION f1(arg1 table.column%TYPE ...
Which, AFAICT, is not possible.
It is definetily possible, i.e:
CREATE OR REPLACE FUNCTION myMaxCycle(i_n_Cell_id GP_CELL.cell_id%TYPE)
RETURNS GP_CYCLE.cycle_date_time%TYPE
AS $$...Works in my 8.2 PG.
After looking in the manual, it seems you're right. Sorry about this.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com