pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedNested-Internal Functions
4+ messages / 4 participants
[nested] [flat]
* Nested-Internal Functions
@ 2024-01-16 12:15 Rossana Ocampos <rocampos@bry-it.com>
0 siblings, 2 replies; 4+ messages in thread
From: Rossana Ocampos @ 2024-01-16 12:15 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello ,
I have a query about creating nested functions in PostgreSQL.
I am currently using PostgreSQL 15 and I am trying to create a nested
function with the following structure:
CREATE OR REPLACE FUNCTION external_function ()
RETURNS void AS $$
DECLARE
external_variable;
-- Define the internal function
FUNCTION internal_function ()
RETURNS void AS $$
DECLARE
internal_variable INT;
BEGIN
-- Internal function code
internal_variable:= 10;
RAISE NOTICE 'Internal Variable: %', internal_variable;
END;
$$ LANGUAGE plpgsql;
BEGIN
-- External function code
external_variable:= 5;
RAISE NOTICE 'External variable: %', external_variable;
-- Call internal function
PERFORM internal_function ();
END;
$$ LANGUAGE plpgsql;
However, I get an error, and I can't compile the function.
Thank you very much for your help
Rossana Ocampos
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Nested-Internal Functions
@ 2024-01-16 13:24 David G. Johnston <david.g.johnston@gmail.com>
parent: Rossana Ocampos <rocampos@bry-it.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2024-01-16 13:24 UTC (permalink / raw)
To: Rossana Ocampos <rocampos@bry-it.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Tuesday, January 16, 2024, Rossana Ocampos <rocampos@bry-it.com> wrote:
> *Hello ,*
>
> *I have a query about creating nested functions in PostgreSQL.*
>
> *I am currently using PostgreSQL 15 and I am trying to create a nested
> function with the following structure:*
>
> *CREATE OR REPLACE FUNCTION external_function ()*
>
> *RETURNS void AS $$*
>
> *DECLARE*
>
> *external_variable;*
>
>
>
> * -- Define the internal function*
>
> * FUNCTION internal_function ()*
>
> * RETURNS void AS $$*
>
> * DECLARE*
>
> * internal_variable INT;*
>
> * BEGIN*
>
> * -- Internal function code*
>
> * internal_variable:= 10;*
>
> * RAISE NOTICE 'Internal Variable: %', internal_variable;*
>
> * END;*
>
> * $$ LANGUAGE plpgsql;*
>
> *BEGIN*
>
> * -- External function code*
>
> * external_variable:= 5;*
>
> * RAISE NOTICE 'External variable: %', external_variable;*
>
>
>
> * -- Call internal function*
>
> * PERFORM internal_function ();*
>
> *END;*
>
> *$$ LANGUAGE plpgsql;*
>
>
>
> However, I get an error, and I can't compile the function.
>
> *Thank you very much for your help*
>
>
You did nested dollar quoting wrong; and I don’t see the word create where
you try to define the function inside the outer function.
You are probably better off just defining two functions independently
anyway, there is minimal benefit to having on function define another in
PostgreSQL, there are no closures.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Nested-Internal Functions
@ 2024-01-16 14:41 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rossana Ocampos <rocampos@bry-it.com>
1 sibling, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2024-01-16 14:41 UTC (permalink / raw)
To: Rossana Ocampos <rocampos@bry-it.com>; pgsql-general@lists.postgresql.org
On Tue, 2024-01-16 at 13:15 +0100, Rossana Ocampos wrote:
> I have a query about creating nested functions in PostgreSQL.
There are no "nested functions" in PostgreSQL.
You'd need to rewrite that to use a proper stand-alone function.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Nested-Internal Functions
@ 2024-01-18 12:34 Pavel Stehule <pavel.stehule@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2024-01-18 12:34 UTC (permalink / raw)
To: Rossana Ocampos <rocampos@bry-it.com>; +Cc: pgsql-general@lists.postgresql.org
Hi
čt 18. 1. 2024 v 13:31 odesílatel Rossana Ocampos <rocampos@bry-it.com>
napsal:
> Effectively I had to create the function externally, I am in the process
> of migrating from Oracle to Postgresql and I have many cases of
> encapsulated functions and transactions.
> Thank you very much for the return.
> Rossana Ocampos
>
There is not any help or possibility - this Oracle's functionality is not
supported ever. There is not any workaround.
Regards
Pavel
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe [mailto:laurenz.albe@cybertec.at]
> Gesendet: Dienstag, 16. Januar 2024 15:42
> An: Rossana Ocampos; pgsql-general@lists.postgresql.org
> Betreff: Re: Nested-Internal Functions
>
> On Tue, 2024-01-16 at 13:15 +0100, Rossana Ocampos wrote:
> > I have a query about creating nested functions in PostgreSQL.
>
> There are no "nested functions" in PostgreSQL.
> You'd need to rewrite that to use a proper stand-alone function.
>
> Yours,
> Laurenz Albe
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-01-18 12:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-16 12:15 Nested-Internal Functions Rossana Ocampos <rocampos@bry-it.com>
2024-01-16 13:24 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-16 14:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-18 12:34 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox