Re: syntax question

[Marc Millas]

no pb: I am french, so quite skilled on that topic :-)
there is only 50 bottles of various malt on the presentoir close to my desk
so I must stay reasonnable :-)



Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Jun 3, 2021 at 11:17 PM Adrian Klaver 
wrote:

> On 6/3/21 1:01 PM, Marc Millas wrote:
> > thanks Adrian, exactly what I was missing :-)
> >
> > about knowing if I should...
> > We have to create a set of triggers (insert, update, delete) within a
> > huge set of tables. and that list of tables, and structure of them  can
> > be customized, maintained, ...
> > so we were looking for a standard script to automatize the building of
> > the whole thing, taking list of columns  and constraints (for PK)
> > directly from pg_catalog.
> > Now it works :-)
> >
> > but.. why do you ask that question ? is there any king of hidden wolf we
> > didnt see ?
>
> See David Johnston's answer. Nested quoting will drive you to drink(or
> drink more):)
>
>
> >
> >
> > Marc MILLAS
> > Senior Architect
> > +33607850334
> > www.mokadb.com 
> >
> >
> >
> > On Thu, Jun 3, 2021 at 9:11 PM Adrian Klaver  > > wrote:
> >
> > On 6/3/21 12:01 PM, Bruce Momjian wrote:
> >  > On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> >  >> Hi,
> >  >>
> >  >> within a function, I want to create another function.
> >  >> no pb.
> >  >> but if I write:
> >  >> declare bidule text;
> >  >> begin
> >  >> bidule:='myfunc';
> >  >> create function bidule() ...
> >  >>
> >  >>
> >  >> it does create a function named bidule and not myfunc.
> >  >> so I am obviously missing something too obvious.
> >  >
> >  > You can't create functions inside of functions;  same for
> procedures.
> >  >
> >
> > Sure you can:
> >
> > CREATE OR REPLACE FUNCTION public.test_fnc()
> >RETURNS void
> >LANGUAGE plpgsql
> > AS $function$
> > DECLARE
> >bidule text;
> > BEGIN
> > bidule:='myfunc';
> > EXECUTE  'create function ' ||  bidule || '() RETURNS void language
> > plpgsql AS $fnc$ BEGIN END; $fnc$ ';
> > END;
> >
> >
> > $function$
> >
> > select test_fnc();
> >test_fnc
> > --
> >
> >\df myfunc
> >   List of functions
> >Schema |  Name  | Result data type | Argument data types | Type
> > ++--+-+--
> >public | myfunc | void | | func
> >
> >
> > Whether you should is another question.
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: syntax question

[Adrian Klaver]

On 6/3/21 1:01 PM, Marc Millas wrote:

thanks Adrian, exactly what I was missing :-)

about knowing if I should...
We have to create a set of triggers (insert, update, delete) within a 
huge set of tables. and that list of tables, and structure of them  can 
be customized, maintained, ...
so we were looking for a standard script to automatize the building of 
the whole thing, taking list of columns  and constraints (for PK) 
directly from pg_catalog.

Now it works :-)

but.. why do you ask that question ? is there any king of hidden wolf we 
didnt see ?


See David Johnston's answer. Nested quoting will drive you to drink(or 
drink more):)






Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 



On Thu, Jun 3, 2021 at 9:11 PM Adrian Klaver > wrote:


On 6/3/21 12:01 PM, Bruce Momjian wrote:
 > On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
 >> Hi,
 >>
 >> within a function, I want to create another function.
 >> no pb.
 >> but if I write:
 >> declare bidule text;
 >> begin
 >> bidule:='myfunc';
 >> create function bidule() ...
 >>
 >>
 >> it does create a function named bidule and not myfunc.
 >> so I am obviously missing something too obvious.
 >
 > You can't create functions inside of functions;  same for procedures.
 >

Sure you can:

CREATE OR REPLACE FUNCTION public.test_fnc()
   RETURNS void
   LANGUAGE plpgsql
AS $function$
DECLARE
       bidule text;
BEGIN
bidule:='myfunc';
EXECUTE  'create function ' ||  bidule || '() RETURNS void language
plpgsql AS $fnc$ BEGIN END; $fnc$ ';
END;


$function$

select test_fnc();
   test_fnc
--

   \df myfunc
                          List of functions
   Schema |  Name  | Result data type | Argument data types | Type
++--+-+--
   public | myfunc | void             |                     | func


Whether you should is another question.


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: syntax question

[Guyren Howe]

I know it would be non-standard, but I would love to see Postgres support the 
likes of nested functions.

I know that would be non-standard, but Postgres has lots of non-standard 
features that make it more like a real programming language and considerably 
more productive.
On Jun 3, 2021, 12:34 -0700, Bruce Momjian , wrote:
> On Thu, Jun 3, 2021 at 03:21:15PM -0400, Tom Lane wrote:
> > Adrian Klaver  writes:
> > > On 6/3/21 12:01 PM, Bruce Momjian wrote:
> > > > On Thu, Jun 3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> > > > > within a function, I want to create another function.
> >
> > > > You can't create functions inside of functions; same for procedures.
> >
> > > Sure you can:
> >
> > Yeah. The actual problem here is that Marc is expecting variable
> > substitution to occur within a utility (DDL) statement, which it
> > doesn't. The workaround is to build the command as a string and
> > use EXECUTE, as Adrian illustrated:
> >
> > > EXECUTE 'create function ' || bidule || '() RETURNS void language
> > > plpgsql AS $fnc$ BEGIN END; $fnc$ ';
> >
> > This is not terribly well explained in the existing docs. I tried
> > to improve the explanation awhile ago in HEAD:
> >
> > https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL
>
> Oh, I thought he wanted to declare a function inside the function that
> could be called only by that function, like private functions in Oracle
> packages can do. Yes, you can create a function that defines a function
> that can be called later. I guess you could also create a function that
> _conditionally_ creates a function that it can call itself too. My
> point is that you can't create a function that has function scope ---
> they all have schema scope.
>
> --
> Bruce Momjian  https://momjian.us
> EDB https://enterprisedb.com
>
> If only the physical world exists, free will is an illusion.
>
>
>

Re: syntax question

[Marc Millas]

I take note of this.
thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Jun 3, 2021 at 10:23 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Jun 3, 2021 at 1:02 PM Marc Millas  wrote:
>
>> about knowing if I should...
>> We have to create a set of triggers (insert, update, delete) within a
>> huge set of tables. and that list of tables, and structure of them  can be
>> customized, maintained, ...
>> so we were looking for a standard script to automatize the building of
>> the whole thing, taking list of columns  and constraints (for PK) directly
>> from pg_catalog.
>> Now it works :-)
>>
>> but.. why do you ask that question ? is there any king of hidden wolf we
>> didnt see ?
>>
>
> Having done this (building a - limited - code generator framework using
> bash+psql+plpgsql) I will say that doing so using pl/pgsql, while appealing
> from "no extra tooling needed" perspective, doesn't play to pl/pgsql's
> strengths.  Using a different language to generate SQL script files, which
> can then be executed, is probably a better way to go - if you have a
> different language you can build upon (i.e., not a shell scripting language
> like bash).
>
> In particular, plpgsql nested strings are not fun to work with in any
> significant volume.
>
> David J.
>
>

Re: syntax question

[David G. Johnston]

On Thu, Jun 3, 2021 at 1:02 PM Marc Millas  wrote:

> about knowing if I should...
> We have to create a set of triggers (insert, update, delete) within a huge
> set of tables. and that list of tables, and structure of them  can be
> customized, maintained, ...
> so we were looking for a standard script to automatize the building of the
> whole thing, taking list of columns  and constraints (for PK) directly from
> pg_catalog.
> Now it works :-)
>
> but.. why do you ask that question ? is there any king of hidden wolf we
> didnt see ?
>

Having done this (building a - limited - code generator framework using
bash+psql+plpgsql) I will say that doing so using pl/pgsql, while appealing
from "no extra tooling needed" perspective, doesn't play to pl/pgsql's
strengths.  Using a different language to generate SQL script files, which
can then be executed, is probably a better way to go - if you have a
different language you can build upon (i.e., not a shell scripting language
like bash).

In particular, plpgsql nested strings are not fun to work with in any
significant volume.

David J.

Re: syntax question

[Marc Millas]

thanks Adrian, exactly what I was missing :-)

about knowing if I should...
We have to create a set of triggers (insert, update, delete) within a huge
set of tables. and that list of tables, and structure of them  can be
customized, maintained, ...
so we were looking for a standard script to automatize the building of the
whole thing, taking list of columns  and constraints (for PK) directly from
pg_catalog.
Now it works :-)

but.. why do you ask that question ? is there any king of hidden wolf we
didnt see ?


Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Jun 3, 2021 at 9:11 PM Adrian Klaver 
wrote:

> On 6/3/21 12:01 PM, Bruce Momjian wrote:
> > On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> >> Hi,
> >>
> >> within a function, I want to create another function.
> >> no pb.
> >> but if I write:
> >> declare bidule text;
> >> begin
> >> bidule:='myfunc';
> >> create function bidule() ...
> >>
> >>
> >> it does create a function named bidule and not myfunc.
> >> so I am obviously missing something too obvious.
> >
> > You can't create functions inside of functions;  same for procedures.
> >
>
> Sure you can:
>
> CREATE OR REPLACE FUNCTION public.test_fnc()
>   RETURNS void
>   LANGUAGE plpgsql
> AS $function$
> DECLARE
>   bidule text;
> BEGIN
> bidule:='myfunc';
> EXECUTE  'create function ' ||  bidule || '() RETURNS void language
> plpgsql AS $fnc$ BEGIN END; $fnc$ ';
> END;
>
>
> $function$
>
> select test_fnc();
>   test_fnc
> --
>
>   \df myfunc
>  List of functions
>   Schema |  Name  | Result data type | Argument data types | Type
> ++--+-+--
>   public | myfunc | void | | func
>
>
> Whether you should is another question.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: syntax question

[Marc Millas]

good reading, thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Jun 3, 2021 at 9:21 PM Tom Lane  wrote:

> Adrian Klaver  writes:
> > On 6/3/21 12:01 PM, Bruce Momjian wrote:
> >> On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> >>> within a function, I want to create another function.
>
> >> You can't create functions inside of functions;  same for procedures.
>
> > Sure you can:
>
> Yeah.  The actual problem here is that Marc is expecting variable
> substitution to occur within a utility (DDL) statement, which it
> doesn't.  The workaround is to build the command as a string and
> use EXECUTE, as Adrian illustrated:
>
> > EXECUTE  'create function ' ||  bidule || '() RETURNS void language
> > plpgsql AS $fnc$ BEGIN END; $fnc$ ';
>
> This is not terribly well explained in the existing docs.  I tried
> to improve the explanation awhile ago in HEAD:
>
>
> https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL
>
> regards, tom lane
>

Re: syntax question

[Bruce Momjian]

On Thu, Jun  3, 2021 at 03:21:15PM -0400, Tom Lane wrote:
> Adrian Klaver  writes:
> > On 6/3/21 12:01 PM, Bruce Momjian wrote:
> >> On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> >>> within a function, I want to create another function.
> 
> >> You can't create functions inside of functions;  same for procedures.
> 
> > Sure you can:
> 
> Yeah.  The actual problem here is that Marc is expecting variable
> substitution to occur within a utility (DDL) statement, which it
> doesn't.  The workaround is to build the command as a string and
> use EXECUTE, as Adrian illustrated:
> 
> > EXECUTE  'create function ' ||  bidule || '() RETURNS void language 
> > plpgsql AS $fnc$ BEGIN END; $fnc$ ';
> 
> This is not terribly well explained in the existing docs.  I tried
> to improve the explanation awhile ago in HEAD:
> 
> https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL

Oh, I thought he wanted to declare a function inside the function that
could be called only by that function, like private functions in Oracle
packages can do.  Yes, you can create a function that defines a function
that can be called later.  I guess you could also create a function that
_conditionally_ creates a function that it can call itself too.  My
point is that you can't create a function that has function scope ---
they all have schema scope.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Re: syntax question

[Tom Lane]

Adrian Klaver  writes:
> On 6/3/21 12:01 PM, Bruce Momjian wrote:
>> On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
>>> within a function, I want to create another function.

>> You can't create functions inside of functions;  same for procedures.

> Sure you can:

Yeah.  The actual problem here is that Marc is expecting variable
substitution to occur within a utility (DDL) statement, which it
doesn't.  The workaround is to build the command as a string and
use EXECUTE, as Adrian illustrated:

> EXECUTE  'create function ' ||  bidule || '() RETURNS void language 
> plpgsql AS $fnc$ BEGIN END; $fnc$ ';

This is not terribly well explained in the existing docs.  I tried
to improve the explanation awhile ago in HEAD:

https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL

regards, tom lane

Re: syntax question

[Adrian Klaver]

On 6/3/21 12:01 PM, Bruce Momjian wrote:

On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:

Hi,

within a function, I want to create another function.
no pb.
but if I write:
declare bidule text;
begin
bidule:='myfunc';
create function bidule() ...


it does create a function named bidule and not myfunc.
so I am obviously missing something too obvious.


You can't create functions inside of functions;  same for procedures.



Sure you can:

CREATE OR REPLACE FUNCTION public.test_fnc()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
 bidule text;
BEGIN
bidule:='myfunc';
EXECUTE  'create function ' ||  bidule || '() RETURNS void language 
plpgsql AS $fnc$ BEGIN END; $fnc$ ';

END;


$function$

select test_fnc();
 test_fnc
--

 \df myfunc
List of functions
 Schema |  Name  | Result data type | Argument data types | Type
++--+-+--
 public | myfunc | void | | func


Whether you should is another question.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: syntax question

[Bruce Momjian]

On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> Hi,
> 
> within a function, I want to create another function.
> no pb.
> but if I write:
> declare bidule text;
> begin
> bidule:='myfunc';
> create function bidule() ...
> 
> 
> it does create a function named bidule and not myfunc.
> so I am obviously missing something too obvious.

You can't create functions inside of functions;  same for procedures.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Re: Syntax question about returning value from an insert

[stan]

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

Re: Syntax question about returning value from an insert

[Adrian Klaver]

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.kla...@aklaver.com

Re: Syntax question about returning value from an insert

[stan]

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 (

Re: Syntax question about returning value from an insert

[stan]

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

Re: Syntax question about returning value from an insert

[Adrian Klaver]

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.kla...@aklaver.com

Re: Syntax question about returning value from an insert

[Rob Sargent]

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

If you know today what those defaults are then load them today. That allows a 
standard FK from t1 to t2.  Also streamlines adding 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.kla...@aklaver.com
> 
> 

Re: Syntax question about returning value from an insert

[Adrian Klaver]

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.kla...@aklaver.com

Re: Syntax question about returning value from an insert

[stan]

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

Re: Syntax question about returning value from an insert

[Adrian Klaver]

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.kla...@aklaver.com

Re: Syntax question about returning value from an insert

[Pavel Stehule]

Hi

st 25. 12. 2019 v 16:26 odesílatel stan  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
>
>
>