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

Urgent Help : Use of return from function/procedure.

From

"Anagha Joshi"

Date:

23 June 2003, 05:42:48

<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.

From

Richard Huxton

Date:

23 June 2003, 10:55:32

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.

From

"Chad Thompson"

Date:

23 June 2003, 16:45:20

----- Original Message -----
From: Anagha Joshi
To: pgsql-sql@postgresql.org
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?

From

Együd Csaba

Date:

26 June 2003, 11:23:26

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?

From

Richard Huxton

Date:

26 June 2003, 14:00:08

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