From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | stan <stanb(at)panix(dot)com> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Syntax question about returning value from an insert |
Date: | 2019-12-25 15:48:54 |
Message-ID: | CAFj8pRBO_vjPZ4Qsff1-NWGa=uSmROcb9GC2vQMDMqTa+OSj1g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
st 25. 12. 2019 v 16:26 odesílatel stan <stanb(at)panix(dot)com> napsal:
> I am writing a trigger/function to make certain a default item, and its key
> exist when an insert is called. EG
>
> The trigger gets called on insert to T1 If column c1 is NULL in the NEW
> structure, I need to check table t2 to get the key associated with the
> default for this column. However, if the default is not yet inserted into
> t2, I an to go ahead and insert it.
>
> I found this page:
>
> https://stackoverflow.com/questions/6560447/can-i-use-return-value-of-insert-returning-in-another-insert
> which sugest this syntax:
>
> with rows as (
> INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
> )
> INSERT INTO Table2 (val)
> SELECT id
> FROM rows
>
> I modified it slightly to look like this:
>
> IF _bom_name_key is NULL
> THEN
> with rows as (
> INSERT INTO project_bom (bom_name) VALUES ('Main') RETURNING
> project_bom_key
> )
> NEW.project_bom_key = SELECT project_bom_key
> FROM rows ;
>
> But this gives me syntax error.
>
you example is little bit confused.
probably it should to be
CREATE OR REPLACE FUNCTION ...
RETURNS ...
AS $$
BEGIN
...
INSERT INTO ... VALUES('...') RETURNING project_bom_key INTO
NEW.project_bom_key;
You cannot to use plpgsql statements inside SQL statements - you cannot to
use assign statement (plpgsql) inside SQL statement (WITH).
> I realize this functionality is slightly different, but can I get the new
> key into the NEW structure to return from the function call?
>
The fields of records are fixed in first time of created composite value,
and cannot to enhanced in time.
But maybe I don't understand well to your use case. Your examples looks
chaotic little bit.
Regards
Pavel
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2019-12-25 16:15:08 | Re: Backup and Restore |
Previous Message | Julien Rouhaud | 2019-12-25 15:36:24 | Re: Question on upgrading postgresql from 10.7 to 11.5 |