I have a function like this:
CREATE OR REPLACE FUNCTION current_name()
RETURNS text AS 'select foo;' LANGUAGE sql;
Except it doesn't work. Neither does RETURN TEXT "SELECT 'foo';"
How can I keep it written in SQL, but still return text?
Add a comment
|
1 Answer
I think this is the least change you need to make it work.
CREATE OR REPLACE FUNCTION current_name()
RETURNS text AS
'select ''foo''::text;'
LANGUAGE sql;
You'll see that the SQL statement--the body of the function--is a string. Strings have to be quoted, and single quotes within a quoted string have to be escaped. Soon you have more quotation marks than actual text.
The usual way to write something like this is to use a dollar quoted string constant.
CREATE OR REPLACE FUNCTION current_name()
RETURNS text AS
$$
select 'foo'::text;
$$
LANGUAGE sql;
answered Apr 22, 2015 at 23:32
Mike Sherrill 'Cat Recall'
96.6k20 gold badges134 silver badges195 bronze badges