Re: Syntax question about returning value from an insert - Mailing list pgsql-general

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.

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