Postgres Pro
Downloads
Home   >   mailing lists

Thread: Nested-Internal Functions

Nested-Internal Functions

From
"Rossana Ocampos"
Date:

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

Aw: Nested-Internal Functions

From
Karsten Hilbert
Date:
> 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

Re: Nested-Internal Functions

From
"David G. Johnston"
Date:
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.
 

Re: Nested-Internal Functions

From
Laurenz Albe
Date:
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

AW: Nested-Internal Functions

From
"Rossana Ocampos"
Date:
Effectively I had to create the function externally, I am in the process of migrating from Oracle to Postgresql and I
havemany 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

Re: Nested-Internal Functions

From
Pavel Stehule
Date:
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