Nested-Internal Functions

I am currently using PostgreSQL 15 and I am trying to create a nested function with the following structure:

...
 

However, I get an error

What *is* the error ?

Karsten

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.

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

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

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

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