1

I have a scenario like below with Postgres 9.4.5: 

CREATE OR REPLACE FUNCTION something(varA text) RETURNS void AS $$
BEGIN
    ...
    RAISE INFO '%',varA;
END;
$$
LANGUAGE plpgsql;

DO
$$
declare
    varA text := :cmd_line_arg;
begin
    perform something(varA);
end;
$$

All this resides in some file plpgsql.sql and is executed like so:

psql -v cmd_line_arg='value' -U postgres -d postgres -f plpgsql.sql

I receive syntax error at or near':'

I have also found errors when using the string concatenation operator ||.

What am I missing here? Is it something to do with the DO?

NOTE: This link did not help.

Community
  • 1
  • 1
Koushik Shetty
  • 2,146
  • 4
  • 20
  • 31

1 Answers1

0

PL/pgSQL error

PL/pgSQL RAISE allows various syntax variants. Yours is not among them. Try instead:

CREATE OR REPLACE FUNCTION something(varA text)
  RETURNS void AS
$func$
BEGIN
   ...
   RAISE INFO '%', varA;
END
$func$  LANGUAGE plpgsql;

psql error

The SQL interpolation feature of psql does not work inside strings. The body of a function or DO command happens to be a plain string. You need to concatenate. Like:

DO
$$
DECLARE
   varA text := $$ || :'cmd_line_arg' || $$
BEGIN
   PERFORM something(varA);
END
$$;

Also note the added quotes in :'cmd_line_arg'.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I realised that and tried to use the concat operator but then it started throwing syntax error for that. its really weird. and about the raise i have corrected it. – Koushik Shetty Dec 13 '15 at 03:39
  • @KoushikShetty: So does the code I suggest still throw an error? Because it works for me ... – Erwin Brandstetter Dec 13 '15 at 10:49
  • yes it still is. i'm suspecting it is something else now. even `language` is throwing up. Is it that it is not being detected as plsql? i dont know anymore – Koushik Shetty Dec 13 '15 at 12:16
  • @KoushikShetty. You probably have more errors. plsql is the Oracle language. It's plpgsql for Postgres, and that should work in a script, generally. – Erwin Brandstetter Dec 13 '15 at 12:21