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 , '%20', ' ');
RETURN temp_text;
$$ LANGUAGE SQL;
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 , '%20', ' ');
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 , '%20', ' ');
end;
$$ LANGUAGE plpgsql;
And you don't even need PL/pgSQL for this:
CREATE FUNCTION uri2text(uri text)
RETURNS text
AS $$
SELECT REPLACE(uri , '%20', ' ');
$$ LANGUAGE sql;
Note that in case of the SQL function, you do need the SELECT because in SQL there is no return or assignment.