1

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.

0

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:

  • Why an out parameter and not a function that is declared as returns text? – a_horse_with_no_name Jun 12 at 3:49
  • Each is valid. This is shorter, no DECLARE section needed. (Question is about assignment, so not returning directly.) – Erwin Brandstetter Jun 12 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.