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

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.

> 
> 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?
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.


-- 
Adrian Klaver
adrian.klaver@aklaver.com