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?

how to declare string inside postgresql function DECLARE SRID character varying; — parlad
– parlad, Commented May 23, 2017 at 15:25

IMSoP · Accepted Answer · 2017-05-23 15:31:10Z

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 ...

I chose the third solution which uses the format function and it worked fine. It also didn't require casting the resulting string to a geometry type. I didn't try the first two solutions. I'm not sure they would work since they don't appear to account for the closing single quote in the string 'SRID=4326;POINT($3 $2)'. Thanks very much!
@Dave All three should result in exactly the same string, unless I've made a typo somewhere. None of them include a "closing single quote" in the string itself, and nor does your original - the last character of your string is ) - so I'm not sure what you think is missing from the || versions in this regard. To understand better what each of these are doing, try just running SELECT 'SRID=4326;POINT(' || 42::text || ' ' || -1::text || ')' as example2, format('SRID=4326;POINT(%s, %s)', 42, -1) as example3, and look at the output.

Collectives™ on Stack Overflow

How to declare a string inside a PostgreSQL function?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related