Обсуждение: 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 > >> 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 > > -- > 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