Re: Syntax question about returning value from an insert

On Wed, Dec 25, 2019 at 09:17:22PM -0800, Adrian Klaver wrote:
> On 12/25/19 4:48 PM, Rob Sargent wrote:
> >
> >
> > > On Dec 25, 2019, at 3:10 PM, stan <stanb(at)panix(dot)com> wrote:
> > > There is more that that. There is a project number, so the actuall key
> > > represents the combination of project number, and cost category, Thire is a
> > > constraint on T2 that assures that these combinations will be unique.
> > >
> > > When the 1st record for a project, that gets charged to the misc. cost
> > > category is entered, I need to assure that combination gets inserted into
> > > T2
> >
> > Is mine the only nose reacting to this? Cost categories aren???t
> > specific to projects are they? Do you need more than a definition of
> > cost categories and then project specific
>
> No, my nose is twitching also.
>
> expenditures use the category id?

Here is this mornings update on this task. I am learning about CTE's, which
is something that was on my need to do list anyway. So I have created this
test script, which works:

WITH inserted AS (
INSERT into project_bom
(project_key, bom_name)
VALUES
(1 , 'test')
RETURNING
project_bom_key
)
SELECT project_bom_key
FROM inserted

BUT, when I try to assign the result of SELECT to either the NEW. record or
a declared variable, such as _bom_name_key , I get syntax error, for
example.

ERROR: syntax error at or near "_bom_name_key"
LINE 35: _bom_name_key = ( SELECT project_bom_key

Is this because the whole CTE segment is a separate "query". How can I
preserve the results of the SELECT to use outside the CTE?

I really appreciate the help you folks are providing.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin