0

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.

Improve this question
1

3 Answers 3

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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;
Improve this answer

Comments

0

You have to escape with double single quote

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.