Apostrophe in EXECUTE postgres

Question

I need to print an empty string using EXECUTE command in Postgres. So, how to circle with quotes the string str in order for Postgres to realize my intention?

CREATE OR REPLACE FUNCTION foo() RETURNS void AS 
$$ 
DECLARE 
empty_str text='';
BEGIN
EXECUTE 'SELECT '||str||'';
END;
$$
LANGUAGE plpgsql;

Table name is a variable in my query.

I want to print nothing at all (not even a carriage return and/or newline). It's a simplified example of my real code where I have to substitute NULL values with empty strings. I use empty string as an option value in NULL 'null_string' in COPY function.

Answer 1

Adding to Andriy's answer, there is a neater way around called dollar quoting. See what the documentation tells us about it:

While the standard syntax for specifying string constants is usually convenient, it can be difficult to understand when the desired string contains many single quotes or backslashes, since each of those must be doubled. To allow more readable queries in such situations, PostgreSQL provides another way, called "dollar quoting", to write string constants. A dollar-quoted string constant consists of a dollar sign ($), an optional "tag" of zero or more characters, another dollar sign, an arbitrary sequence of characters that makes up the string content, a dollar sign, the same tag that began this dollar quote, and a dollar sign.

Using this technique simplifies your life and makes the statement much more readable:

CREATE OR REPLACE FUNCTION foo() RETURNS void AS 
$$ 
DECLARE 
 empty_str text := '';
BEGIN
 EXECUTE $sql$ SELECT ' $sql$ || empty_str || $sql$ ' $sql$;
END;
$$
LANGUAGE plpgsql;

I've added an extra space after and before $sql$ to make things more clear. Here the tag that the documentation mentioned is 'sql'. Note that you are already using dollar quoting: it's the pair of $$s around the function body.

Upon a hint from ypercube, I have to say that in the general case one should use

EXECUTE 'SELECT quote_nullable(1ドル)' USING empty_str;

Read more about quoting in dynamic queries at https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE

Answer 2

If you mean you want your dynamic SELECT statement to look like this:

SELECT 'contents of str'

then you need to include the apostrophes into the query you are building. Since the apostrophes also delimit the dynamic query itself, you need to escape them inside the string in order for them to be treated as part of the string. A common way to do that is to double the apostrophe – that way each pair of them is treated as a single character:

EXECUTE 'SELECT '''||str||'''';
 -- ^^ these ^^ and these represent a single '