syntax question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

syntax question

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

can someone help ?
thanks



Marc MILLAS
Senior Architect
+33607850334

Reply | Threaded
Open this post in threaded view
|

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  <[hidden email]>        https://momjian.us
  EDB                                      https://enterprisedb.com

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



Reply | Threaded
Open this post in threaded view
|

Re: syntax question

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: syntax question

Tom Lane-2
Adrian Klaver <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: syntax question

Bruce Momjian
On Thu, Jun  3, 2021 at 03:21:15PM -0400, Tom Lane wrote:

> Adrian Klaver <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EDB                                      https://enterprisedb.com

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



Reply | Threaded
Open this post in threaded view
|

Re: syntax question

Marc Millas
In reply to this post by Tom Lane-2
good reading, thanks

Marc MILLAS
Senior Architect
+33607850334



On Thu, Jun 3, 2021 at 9:21 PM Tom Lane <[hidden email]> wrote:
Adrian Klaver <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: syntax question

Marc Millas
In reply to this post by Adrian Klaver-4
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



On Thu, Jun 3, 2021 at 9:11 PM Adrian Klaver <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: syntax question

David G Johnston
On Thu, Jun 3, 2021 at 1:02 PM Marc Millas <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: syntax question

Marc Millas
I take note of this.
thanks

Marc MILLAS
Senior Architect
+33607850334



On Thu, Jun 3, 2021 at 10:23 PM David G. Johnston <[hidden email]> wrote:
On Thu, Jun 3, 2021 at 1:02 PM Marc Millas <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: syntax question

Guyren Howe
In reply to this post by Bruce Momjian
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 <[hidden email]>, wrote:
On Thu, Jun 3, 2021 at 03:21:15PM -0400, Tom Lane wrote:
Adrian Klaver <[hidden email]> 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 <[hidden email]> https://momjian.us
EDB https://enterprisedb.com

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



Reply | Threaded
Open this post in threaded view
|

Re: syntax question

Adrian Klaver-4
In reply to this post by Marc Millas
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 <http://www.mokadb.com>
>
>
>
> On Thu, Jun 3, 2021 at 9:11 PM Adrian Klaver <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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



On Thu, Jun 3, 2021 at 11:17 PM Adrian Klaver <[hidden email]> 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 <http://www.mokadb.com>
>
>
>
> On Thu, Jun 3, 2021 at 9:11 PM Adrian Klaver <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

inner subprograms ... Was: syntax question

Bryn Llewellyn
In reply to this post by Bruce Momjian
[hidden email] wrote:

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.

I’ve heard that EDB’s version of PostgreSQL supports inner subprograms (declared and defined within a DECLARE section) to any depth of nesting—and packages too. Is this true?

I worked at Oracle HQ for the longest time. Not a day goes by, when I need to write PL/pgSQL code, that I don’t miss these two constructs. I wish that (a future version of) vanilla PG could bring support for them.