pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Nested-Internal Functions
4+ messages / 4 participants
[nested] [flat]

* Nested-Internal Functions
@ 2024-01-16 12:15 Rossana Ocampos <rocampos@bry-it.com>
  2024-01-16 13:24 ` Re: Nested-Internal Functions David G. Johnston <david.g.johnston@gmail.com>
  2024-01-16 14:41 ` Re: Nested-Internal Functions Laurenz Albe <laurenz.albe@cybertec.at>
  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 12:15 Nested-Internal Functions Rossana Ocampos <rocampos@bry-it.com>
@ 2024-01-16 13:24 ` David G. Johnston <david.g.johnston@gmail.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 12:15 Nested-Internal Functions Rossana Ocampos <rocampos@bry-it.com>
@ 2024-01-16 14:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
  2024-01-18 12:34   ` Re: Nested-Internal Functions Pavel Stehule <pavel.stehule@gmail.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-16 12:15 Nested-Internal Functions Rossana Ocampos <rocampos@bry-it.com>
  2024-01-16 14:41 ` Re: Nested-Internal Functions Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-01-18 12:34   ` Pavel Stehule <pavel.stehule@gmail.com>
  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