PostgreSQL: Access enclosing function's argument inside DO block

Question

I have an SQL function which has a DO block, and when trying to access an argument of the enclosing function inside the DO block I get a " column does not exist" error. What am I missing?

CREATE OR REPLACE FUNCTION f1(a1 TEXT) RETURNS VOID AS $$
DO $DO$
BEGIN
    RAISE NOTICE 'a1 = %', a1;
END
$DO$;
$$ LANGUAGE SQL VOLATILE;

SELECT f1('AA');

ERROR: column "a1" does not exist SQL state: 42703 Context: PL/pgSQL function inline_code_block line 3 at RAISE SQL function "f1" statement 1

This doesn't make sense. If you want to use PL/pgSQL, then why don't you simply make that function a PL/pgSQL function? — user330315
– user330315, Commented Nov 2, 2017 at 11:27
My actual function already contains some significant SQL code, I'd rather not drop and re-create it as plpgsql. — Isac Casapu
– Isac Casapu, Commented Nov 2, 2017 at 11:32
A SQL function can only contain a single SQL statement, so there is not much work involved to turn that into a PL/pgSQL function. You simply add a begin ...end, put a return query in front of the select and change language sql to language plpgsql. You apparently need PL/pgSQL otherwise you wouldn't have the DO block. — user330315
– user330315, Commented Nov 2, 2017 at 11:37
The original function does contain several SQL statements. I'm aware of the fact that only the last one can return values. Anyway, I did drop the function and create is as PLPGSQL. — Isac Casapu
– Isac Casapu, Commented Nov 2, 2017 at 11:39

klin · Accepted Answer · 2017-11-02 20:12:52Z

1

I guess you want to have a possibility to raise a notice in an SQL function. The DO block however is not a good idea, because the function's arguments are not visible inside it. Instead, you can prepare an auxiliary function that raises a notice:

create or replace function raise_notice(anyelement)
returns void language plpgsql as $$
begin
    raise notice '%', $1;
end $$;

Now you can use it in an SQL function, e.g.:

create or replace function f1(a1 text)
returns text language sql as $$
    select raise_notice(a1);
    select a1;
$$;

select f1('some text');

NOTICE:  some text
CONTEXT:  PL/pgSQL function raise_notice(anyelement) line 3 at RAISE
SQL function "f1" statement 1
    f1     
-----------
 some text
(1 row)

answered Nov 2, 2017 at 20:12

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Isac Casapu Over a year ago

Actaully no... My original intention was to keep as much as possible in a pure SQL function, and only put plpgsql in a DO block, but seems like this mixing doesn't work, so I've changed the whole function to plpgsql.

Pavel Stehule · Accepted Answer · 2017-11-03 07:32:43Z

0

You has wrong language specification

$$ LANGUAGE SQL VOLATILE;

should be:

$$ LANGUAGE plpgsql VOLATILE;

The DO statement is new, and there is plpgsql language by default.

answered Nov 3, 2017 at 7:32

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Access enclosing function's argument inside DO block

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related