Re: Syntax question about returning value from an insert

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.

That's what I have in mind.

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