Обсуждение: Urgent Help : Use of return from function/procedure.

Поиск
Список
Период
Сортировка

Urgent Help : Use of return from function/procedure.

От
"Anagha Joshi"
Дата:
<p><font face="Arial" size="2">Hi,</font><br /><font face="Arial" size="2">I'm new to postgres and using version
7.2.4</font><p><fontface="Arial" size="2">I've created a trigger and function which does the following:</font><br
/>       <font face="Arial" size="2">trigger 'T' fires after insert on a spcific table takes place and it executes
function'F'</font><p><font face="Arial" size="2">Function 'F' returns the new record inserted by 'return new'
statement.</font><p><fontface="Arial" size="2">Now my question is:</font><br /><font face="Arial" size="2">How can I
usethis 'new' value in my client program? In this prgm., I want to know which all values are inserted into the
table.</font><p><fontface="Arial" size="2">Help is appreciated.</font><p><font face="Arial" size="2">Thx,</font><br
/><fontface="Arial" size="2">Anagha</font>

Re: Urgent Help : Use of return from function/procedure.

От
Richard Huxton
Дата:
On Monday 23 Jun 2003 6:42 am, Anagha Joshi wrote:
> Hi,
> I'm new to postgres and using version 7.2.4
>
> I've created a trigger and function which does the following:
>     trigger 'T' fires after insert on a spcific table takes place
> and it executes function 'F'
>
> Function 'F' returns the new record inserted by 'return new' statement.
>
> Now my question is:
> How can I use this 'new' value in my client program? In this prgm., I
> want to know which all values are inserted into the table.

Short answer - you don't. I'm not sure why you want to do this - if you just
inserted the new values, surely you know what they are?

There was something very similar to this last week, I'd have a look in the
mailing-list archives at http://archives.postgresql.org - it was either this
list or "general".

--  Richard Huxton

Re: Urgent Help : Use of return from function/procedure.

От
"Chad Thompson"
Дата:
 
----- Original Message -----
Sent: Sunday, June 22, 2003 11:42 PM
Subject: [SQL] Urgent Help : Use of return from function/procedure.

Hi,
I'm new to postgres and using version 7.2.4

I've created a trigger and function which does the following:
        trigger 'T' fires after insert on a spcific table takes place and it executes function 'F'

Function 'F' returns the new record inserted by 'return new' statement.

Now my question is:
How can I use this 'new' value in my client program? In this prgm., I want to know which all values are inserted into the table.

Help is appreciated.

Thx,
Anagha

 
Im not sure if this is what you are looking for.  But I use postgres this way to know which record I have sent.  Its a very simple function and should be self explanitory.  I think the GET DIAGNOSTICS is the key for you in this case.
 
 
-- Function: public.return_mortgage_id(varchar, varchar)
CREATE
 FUNCTION public.return_mortgage_id(varchar, varchar) RETURNS int8 AS '
DECLARE
oid1 INTEGER;
retval integer;
BEGIN
insert
 into mortgage(contact_firstname, contact_lastname, date_submitted)
values($1,$2, now());
GET DIAGNOSTICS oid1 = RESULT_OID;
select
 id into retval
from
 mortgage
where
 oid = oid1;
return retval;
end;' LANGUAGE 'plpgsql' IMMUTABLE;
 
Hope that helps
Chad

Join or Where?

От
Együd Csaba
Дата:
Hi,
could anybody tell me what can be the difference between these queries?
There are a big difference but I can't understand the reasons. For some
reasons the firs query calculates getup column in a wrong way. It allways
devides with 10. (???) While the getupquantity column comes well.

With the second query everything is ok.

Thank you,

-- Csaba

----------------------------------------------------------------------------
-------
select (select getupquantity from t_products where id=productid) as
getupquantity,
       quantity,
       (select quantity/getupquantity) as getup
from t_stockchanges, t_products
where (getupquantity<>0)
limit 30;

RESULT:
 getupquantity | quantity | getup
---------------+----------+-------
            10 |      100 |    10
            10 |    10600 |  1060
            15 |    15150 |  1515
            13 |    13650 |  1365
          17.5 |    17500 |  1750
          5.75 |     5750 |   575
           2.5 |     2500 |   250
           1.5 |     1500 |   150
            16 |    16000 |  1600
             2 |     2000 |   200
           5.5 |     5500 |   550
           4.5 |     4500 |   450
             2 |     2000 |   200
----------------------------------------------------------------------------
-------
select t_products.getupquantity as getupquantity,
       quantity,
       (select t_stockchanges.quantity/t_products.getupquantity) as getup
from t_stockchanges
join t_products on (t_products.id=productid)
where (getupquantity<>0)
limit 30;

RESULT:
getupquantity | quantity | getup
---------------+----------+-------
            10 |      100 |    10
            10 |    10600 |  1060
            15 |    15150 |  1010
            13 |    13650 |  1050
          17.5 |    17500 |  1000
          5.75 |     5750 |  1000
           2.5 |     2500 |  1000
           1.5 |     1500 |  1000
            16 |    16000 |  1000
             2 |     2000 |  1000
           5.5 |     5500 |  1000
           4.5 |     4500 |  1000
             2 |     2000 |  1000
--------------------------------------------------------------------
Hi,
could anybody tell me what can be the difference between these queries?
There are a big difference but I can't understand the reasons. For some
reasons the firs query calculates getup column in a wrong way. It allways
devides with 10. (???) While the getupquantity column comes well.

With the second query everything is ok.

Thank you,

-- Csaba

----------------------------------------------------------------------------
-------
select (select getupquantity from t_products where id=productid) as
getupquantity,
       quantity,
       (select quantity/getupquantity) as getup
from t_stockchanges, t_products
where (getupquantity<>0)
limit 30;

RESULT:
 getupquantity | quantity | getup
---------------+----------+-------
            10 |      100 |    10
            10 |    10600 |  1060
            15 |    15150 |  1515
            13 |    13650 |  1365
          17.5 |    17500 |  1750
          5.75 |     5750 |   575
           2.5 |     2500 |   250
           1.5 |     1500 |   150
            16 |    16000 |  1600
             2 |     2000 |   200
           5.5 |     5500 |   550
           4.5 |     4500 |   450
             2 |     2000 |   200
----------------------------------------------------------------------------
-------
select t_products.getupquantity as getupquantity,
       quantity,
       (select t_stockchanges.quantity/t_products.getupquantity) as getup
from t_stockchanges
join t_products on (t_products.id=productid)
where (getupquantity<>0)
limit 30;

RESULT:
getupquantity | quantity | getup
---------------+----------+-------
            10 |      100 |    10
            10 |    10600 |  1060
            15 |    15150 |  1010
            13 |    13650 |  1050
          17.5 |    17500 |  1000
          5.75 |     5750 |  1000
           2.5 |     2500 |  1000
           1.5 |     1500 |  1000
            16 |    16000 |  1000
             2 |     2000 |  1000
           5.5 |     5500 |  1000
           4.5 |     4500 |  1000
             2 |     2000 |  1000
--------------------------------------------------------------------

Re: Join or Where?

От
Richard Huxton
Дата:
On Thursday 26 Jun 2003 12:21 pm, Együd Csaba wrote:
> Urgent Help : Use of return from function/procedure.Hi,
> could anybody tell me what can be the difference between these queries?
> There are a big difference but I can't understand the reasons. For some
> reasons the firs query calculates getup column in a wrong way. It allways
> devides with 10. (???) While the getupquantity column comes well.
>
> With the second query everything is ok.

1 > select (select getupquantity from t_products where id=productid) as
2 > getupquantity,
3 >        quantity,
4 >        (select quantity/getupquantity) as getup
5 > from t_stockchanges, t_products
6 > where (getupquantity<>0)
7 > limit 30;

I don't think the t_products in the first line is the same as that in the
fifth line - that'd surely mess up your values.

--  Richard Huxton