How to declare a string inside a PostgreSQL function?

Question

I'm trying to construct a PostgreSQL function that updates the longitude and latitude and sets a PostGIS geometry point using a lat/lon pair for a given user:

CREATE OR REPLACE FUNCTION set_user_geom(uid integer, lat double precision, lon double precision)
RETURNS void AS
$$
UPDATE profile SET home_lon = $3 WHERE user_id = $1;
UPDATE profile SET home_lat = $2 WHERE user_id = $1;
UPDATE profile SET home_point = 'SRID=4326;POINT($3 $2)' WHERE user_id = $1;
$$
LANGUAGE 'sql';

When I try to create the functions in the psql console I get the following error:

ERROR: parse error - invalid geometry
LINE 6: UPDATE personal_profile SET home_point = 'SRID=4326;POINT($3...
                                                 ^
HINT: "SRID=4326;POINT($3)" <-- parse error at position 18 within geometry

I've reviewed the PostgreSQL documentation but didn't find an answer. I also tried escaping each single quote with another single quote as well as with a backslash but that didn't fix it.

If I run the command that sets home_point from the command line with valid arguments, it works just fine.

Can anyone see what I'm doing wrong?

Answer 1

Postgres doesn't do any string interpolation: the string 'SRID=4326;POINT($3 $2)' means literally 'SRID=4326;POINT($3 $2)', but you want it to actually "fill in" the $3 and $2, so it becomes something like 'SRID=4326;POINT(-10.2 42.5)'.

All you need to do is concatenate the variables and the fixed part of the string, using the || operator (don't forget the space). However, you'll also need to convert them to text to avoid type mismatch errors:

... SET home_point = 'SRID=4326;POINT(' || Cast($3 as text) || ' ' || Cast($2 as text) || ')' ...

Or using Postgres's non-standard :: cast operator, which I find quite readable:

... SET home_point = 'SRID=4326;POINT(' || $3::text || ' ' || $2::text || ')' ...

There's also a format function which works a bit like sprintf: you give it a string with some placeholders, and it fills in the parts. It accepts any argument type, doesn't require you to cast to text first:

... SET home_point = format('SRID=4326;POINT(%s, %s)', $3, $2) ...

You may then have to cast the concatenated string explicitly to geometry type:

... SET home_point = Cast( 'SRID=4326;POINT(' || Cast($3 as text) || ' ' || Cast($2 as text) || ')' as geometry ) ...
... SET home_point = ('SRID=4326;POINT(' || $3::text || ' ' || $2::text || ')')::geometry ...
... SET home_point = Cast(format('SRID=4326;POINT(%s, %s)', $3, $2) as geometry) ...
... SET home_point = format('SRID=4326;POINT(%s, %s)', $3, $2)::geometry ...