Postgres function: assiging results from built-in function to variable-CodePudding

How to assign results of REPLACE() built-in Postgres function to another variable? I am trying something like this, but it does not work:

CREATE FUNCTION uri2text(uri text) RETURNS text AS $$
SET temp_text = SELECT REPLACE(uri , ' ', ' ');
RETURN temp_text;
$$ LANGUAGE SQL;

Thanks for any kind help :)

CodePudding user response：

You can't have variables in SQL (which is the language that language sql selects for the function). To use variables, you need PL/pgSQL, and as documented in the manual assignment is done using := (or =) in PL/pgSQL. And you need to declare a variable before you can use it.

You also don't need a SELECT statement to call a function.

So if you do want to use PL/pgSQL, the function needs to look like this:

CREATE FUNCTION uri2text(uri text) 
  RETURNS text 
AS $$
declare
  temp_text text;
begin
  temp_text := REPLACE(uri , ' ', ' ');
  RETURN temp_text;
end;
$$ LANGUAGE plpgsql;

However you wouldn't even need a variable:

CREATE FUNCTION uri2text(uri text) 
  RETURNS text 
AS $$
begin
  RETURN REPLACE(uri , ' ', ' ');
end;
$$ LANGUAGE plpgsql;

And you don't even need PL/pgSQL for this:

CREATE FUNCTION uri2text(uri text) 
  RETURNS text 
AS $$
 SELECT REPLACE(uri , ' ', ' ');
$$ LANGUAGE sql;

Note that in case of the SQL function, you do need the SELECT because in SQL there is no return or assignment.