PLPGSQL – Assign Variable Based on SQL Function Result in PostgreSQL

functionsplpgsqlpostgresql

I have a PLPGSQL function which receives an address in two parts:

num = '123'
location = 'main street, anytown, KS'

To utilize the various portions of location I found there is a SQL function SPLIT_PART() which would work how I want it to, in that, if I execute
SPLIT_PART(location,',',2) it would render the 'city' portion.

I've tested this SPLIT_PART() function in pgAdmin4 to verify that it works how I expect it to, but I am having trouble executing this statement within the PLPGSQL environment and assigning the resulting value to a variable.

Through my searching, it appears as though using 'SELECT ... INTO ... ' is what I want to be using, but I have tried running this directly, using EXECUTE or EXECUTE QUERY only render errors. the latest being: 

ERROR:  syntax error at or near "SPLIT_PART"

Attempt 1

DECLARE
    cn_rec RECORD;
BEGIN
    SELECT split_part INTO cn_rec FROM SPLIT_PART('''|| i_location ||''', '','',2);
--ERROR:  function split_part(unknown, unknown, unknown, integer) does not exist
--LINE 1: SELECT split_part             FROM SPLIT_PART('''|| i_locati...
--                                           ^
END;

Attempt 2

DECLARE
    cn_rec RECORD;
BEGIN
     EXECUTE QUERY 'SELECT split_part INTO cn_rec FROM SPLIT_PART('''|| i_location ||''', '','',2)';
--ERROR:  type "query" does not exist
--LINE 1: SELECT QUERY 'SELECT split_part INTO cn_rec FROM SPLIT_PART(...
--               ^
END;

Attempt 3

DECLARE
    cn_rec RECORD;
BEGIN
    EXECUTE 'SPLIT_PART('''|| i_location ||''', '','',2)' INTO cn_rec;
--ERROR:  syntax error at or near "SPLIT_PART"
--LINE 1: SPLIT_PART('Main St,Anytown', ',',2)
--        ^
END;

Being fairly new to PLPGSQL I'm not sure if I'm up against syntax errors, or fundamental misunderstanding of how this should work.

Best Answer

While you can use SELECT INTO, it's simpler to use the assignment operator := (or just =) to assign a single value:

_city := split_part(_location, ', ', 2);

EXECUTE would only be needed for dynamic SQL (building and executing SQL strings dynamically). Not for a simple assignment.

Demo

CREATE OR REPLACE FUNCTION f_dummy(_location text, OUT _city text) AS
$func$
BEGIN
   _city := split_part(_location, ', ', 2);
END
$func$  LANGUAGE plpgsql;

SELECT f_dummy('main street, anytown, KS');  -- 'anytown'

db<>fiddle here

Related:

Related Question