can't use string in function at postgresql

Question

I have some problem with postgresql functions. My table name is people and name is text and year is integer.

I like to write a function like this:

create function add() returns void as '
insert into people(name, year) VALUES ('danilo', 12)

' LANGUAGE SQL;

I can't insert string, like danilo.

ERROR: syntax error at or near "danilo"

I tried

...
insert into people(name, year) VALUES ( \'danilo\', 12)
...

but doesn't works.

this works perfectly:

...insert into people( year) VALUES ( 12)...

and this too:

create function add(text) returns void as '
insert into people(name, year) VALUES ($1, 12)

' LANGUAGE SQL;

select add('danilo');

but how can I do something like:

...
insert into people(name, year) VALUES ('danilo', 12)
...

Can anyone help me?

Thank you.

Answer 1

Your case is good example, why PostgreSQL has custom string separators - symbol $some$. Custom string separator should be used in pair.

postgres=# select $_$Hello$_$;
 ?column? 
----------
 Hello
(1 row)

There are no issues with apostrophes

postgres=# select $_$He'llo$_$;
 ?column? 
----------
 He'llo
(1 row)

So your code should to look like

create function add(text)
returns void as $$
insert into people(name, year) VALUES ('Hello', 12)
$$ LANGUAGE SQL;

Answer 2

I have the answer:

Use ''danilo'', double the '

Escaping single quotes ' by doubling them up ''

create function add() returns void as '
insert into people(name, year) VALUES (''danilo'', 12)

' LANGUAGE SQL;

Answer 3

You have to escape with double single quote

create function add() returns void as '
insert into people(name, year) VALUES (''danilo'', 12)
' LANGUAGE SQL;