Thread: Syntax question about returning value from an insert
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 found this page: https://stackoverflow.com/questions/6560447/can-i-use-return-value-of-insert-returning-in-another-insert which sugest this syntax: with rows as ( INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id ) INSERT INTO Table2 (val) SELECT id FROM rows I modified it slightly to look like this: IF _bom_name_key is NULL THEN with rows as ( INSERT INTO project_bom (bom_name) VALUES ('Main') RETURNING project_bom_key ) NEW.project_bom_key = SELECT project_bom_key FROM rows ; But this gives me syntax error. I realize this functionality is slightly different, but can I get the new key into the NEW structure to return from the function call? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Hi
st 25. 12. 2019 v 16:26 odesílatel stan <stanb@panix.com> napsal:
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 found this page:
https://stackoverflow.com/questions/6560447/can-i-use-return-value-of-insert-returning-in-another-insert
which sugest this syntax:
with rows as (
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
)
INSERT INTO Table2 (val)
SELECT id
FROM rows
I modified it slightly to look like this:
IF _bom_name_key is NULL
THEN
with rows as (
INSERT INTO project_bom (bom_name) VALUES ('Main') RETURNING
project_bom_key
)
NEW.project_bom_key = SELECT project_bom_key
FROM rows ;
But this gives me syntax error.
you example is little bit confused.
probably it should to be
CREATE OR REPLACE FUNCTION ...
RETURNS ...
AS $$
BEGIN
...
INSERT INTO ... VALUES('...') RETURNING project_bom_key INTO NEW.project_bom_key;
You cannot to use plpgsql statements inside SQL statements - you cannot to use assign statement (plpgsql) inside SQL statement (WITH).
I realize this functionality is slightly different, but can I get the new
key into the NEW structure to return from the function call?
The fields of records are fixed in first time of created composite value, and cannot to enhanced in time.
But maybe I don't understand well to your use case. Your examples looks chaotic little bit.
Regards
Pavel
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
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? > > I found this page: > https://stackoverflow.com/questions/6560447/can-i-use-return-value-of-insert-returning-in-another-insert > which sugest this syntax: > > with rows as ( > INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id > ) > INSERT INTO Table2 (val) > SELECT id > FROM rows > > I modified it slightly to look like this: > > IF _bom_name_key is NULL > THEN > with rows as ( > INSERT INTO project_bom (bom_name) VALUES ('Main') RETURNING > project_bom_key > ) > NEW.project_bom_key = SELECT project_bom_key > FROM rows ; > > But this gives me syntax error. > > I realize this functionality is slightly different, but can I get the new > key into the NEW structure to return from the function call? > > -- Adrian Klaver adrian.klaver@aklaver.com
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?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
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. -- Adrian Klaver adrian.klaver@aklaver.com
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
> On Dec 25, 2019, at 11:56 AM, Adrian Klaver < >> 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 asintended e.g. have t2 be the parent table and INSERT the correct type/key combination there first before you INSERT intot1, as separate operations. As you script it out above you have to know what the the type/key is before you INSERT intot1 anyway. > > If you know today what those defaults are then load them today. That allows a standard FK from t1 to t2. Also streamlinesadding new values (no code required). Your current plan is at risk of typos causing new bogus defaults. Are your multiple non-null columns each a separate domain referencing separate “t2”s? > -- > Adrian Klaver > adrian.klaver@aklaver.com > >
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
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
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
> On Dec 25, 2019, at 3:10 PM, stan <stanb@panix.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 definitionof cost categories and then project specific expenditures use the category id? > >
On 12/25/19 3:09 PM, stan wrote: > On Wed, Dec 25, 2019 at 02:34:43PM -0800, Adrian Klaver wrote: >> On 12/25/19 12:39 PM, stan wrote: >>> >> >> 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. So you have the unique key for the parent record of a FK relationship. > > 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 When you start a new project seed it with the project number, cost category, type='misc' record. > >> 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
On 12/25/19 4:48 PM, Rob Sargent wrote: > > >> On Dec 25, 2019, at 3:10 PM, stan <stanb@panix.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 definitionof cost categories and then project specific No, my nose is twitching also. expenditures use the category id? >> >> > -- Adrian Klaver adrian.klaver@aklaver.com
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@panix.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