CODEWITHSUNDEEP
sqlpostgresqlplpgsql
St.Antario
St.Antario

Reputation: 27435

Can we declare variables inside a begin-end block of plpgsql function?

I tried to write the simple function:

CREATE OR REPLACE FUNCTION add_mail_settings_column() RETURNS void AS $$
BEGIN
    asd text := 'asd';
END $$ 
LANGUAGE plpgsql;

But it doesn't work:

ERROR:  syntax error at or near "asd"
LINE 3:  asd text := 'asd';

But if I move it as follows:

CREATE OR REPLACE FUNCTION add_mail_settings_column() RETURNS void AS $$
DECLARE
    asd text := 'asd';
BEGIN

END $$ 
LANGUAGE plpgsql;

It works fine. So we can't put variable declarations into a function body can we?

Upvotes: 2

Views: 6520

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95741

You can declare variables only in the DECLARE section of a block. But you can code blocks inside blocks. This is copied directly from the PostgreSQL docs on the structure of PL/pgSQL.

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

Upvotes: 14

Related Questions