syntax error when running postgresql function/stored procedure

Question

Trying to perform a set of complex Postgresql DB operations by using function, but even a simple function gets me error:

ERROR:  syntax error at or near "text"
LINE 3:    tmp text := info;
               ^

Here is SQL

CREATE or REPLACE FUNCTION createme (info text) RETURNS text AS $$
DECLARE 
   tmp text := info;
BEGIN
   select :tmp
end
$$ LANGUAGE SQL;

Any idea why? Thx!

Pavel Stehule · Accepted Answer · 2017-09-12 03:54:59Z

3

You procedure is not in SQL language, but it is in plpgsql language.

CREATE or REPLACE FUNCTION createme (info text) RETURNS text AS $$
DECLARE 
  tmp text := info;
BEGIN
  RETURN tmp;
end
$$ LANGUAGE plpgsql;

SELECT :tmp is nonsense in this content. Functions returns a value with command RETURN - it is similar to any other environments.

answered Sep 12, 2017 at 3:54

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

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

1 Comment

Thanks @pavel-stehule !