Re: Syntax question about returning value from an insert

On Wed, Dec 25, 2019 at 06:09:55PM -0500, stan wrote:
> On Wed, Dec 25, 2019 at 02:34:43PM -0800, Adrian Klaver wrote:
> > On 12/25/19 12:39 PM, stan wrote:
> > >
> > > On Wed, Dec 25, 2019 at 11:55:51AM -0800, Adrian Klaver wrote:
> > > > On 12/25/19 11:08 AM, stan wrote:
> > > > >
> > > > > On Wed, Dec 25, 2019 at 08:28:45AM -0800, Adrian Klaver wrote:
> > > > > > On 12/25/19 7:26 AM, stan wrote:
> > > > > > > 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'm with Pavel in not understanding what you want to do. This prevents any
> > > > > > clear discussion on what to do below. To help:
> > > > > >
> > > > > > 1) Schema of t1 and t2.
> > > > > >
> > > > > > 2) Default for what column?
> > > > > >
> > > > > > 3) What is the key?
> > > > > >
> > > > >
> > > > > First of all, thanks to both of you for your fast response .
> > > > >
> > > > > Let me clarify.
> > > > >
> > > > > I have a table that records will be inserted into. Several of the columns
> > > > > in this table must be non NULL, and they are actually keys from other
> > > > > tables. Like
> > > > >
> > > > > Table t1
> > > > > has a column like cost_category_key
> > > > >
> > > > > So if an INSERT to this table gets called with this column as a NULL, I am
> > > > > creating a function that will query for the default category, like
> > > > >
> > > > > SELECT cost_category_key from t2 where type = 'Misc'
> > > > >
> > > > > Now suppose that the default category has not yet been inserted in T2. I
> > > > > can easily detect this as the SELECT will return a NULL. So what I want to
> > > > > do is go ahead and insert this row. Once this is done, the correct default
> > > > > row will exist in T2, but I still need the (automatically assigned) key for
> > > > > this row to place in the NEW. structure for the function that is called  On
> > > > > insert to t1, and checks to see if the value supplied for this key is in
> > > > > t2.
> > > > >
> > > > > Make more sense?
> > > >
> > > > No. It looks like you are trying to do a backwards FK. I would say your life
> > > > would be a lot easier if you used FK's as intended e.g. have t2 be the
> > > > parent table and INSERT the correct type/key combination there first before
> > > > you INSERT into t1, as separate operations. As you script it out above you
> > > > have to know what the the type/key is before you INSERT into t1 anyway.
> > >
> > > No, the key is auto generated using a sequence on the INSERT into t2
> > >
> > > Which is where this gets interesting. If I try to select for it and a NULL
> > > is returned, then I KNOW I need to insert it. As a consequence of this,
> > > the new key is generated. I need to populate (replace the NULL) that comes
> > > in the NEW. record with the newly generated key.
> > >
> > > The original article says that I can get the results of an insert, to use.
> >
> > You can given the correct conditions. Since you did not include the syntax
> > error in the original post it is is difficult to say what the problem is and
> > I'm not going to just throwing out guesses.
>
> Sorry, I was trying to just get across what I am trying to do without
> writing in large unrelated sections.
> >
> > >
> > > That's what I have in mind.
> > >
> > > Other approaches welcome.
> >
> > Other approaches have been suggested, e.g. use a traditional FK
> > relationship. The big unknown in you present system is what:
> >
> > SELECT cost_category_key from t2 where type = 'Misc'
> >
> > will return. The implication is that it maybe more then one value(key) in
> > that case, which key would you use?
>
> 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
>
> > If not and  cost_category_key: type is one to one then why have both?
> > Just make one unique(or PK) and the parent for the cost_category_key in t1.
>
Slightly simpler example WITH the syntax error. here is the syntax error

psql:src/functions.sql:948: ERROR:  syntax error at or near "with"
LINE 28: with inserted as (

snippet of function:

NEW.project_bom_key  =
with inserted as (
insert into project_cost_category (category)
values('MISC') returning project_cost_category_key
)


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