Create function that returns an array of text

Question

I tried creating a function that would return the contents of the below query where proto_location is of type text. Not sure if it matters but my java code that currently calls this query just reads each record as a string.

SELECT DISTINCT tm.proto_location
FROM track_message tm
WHERE tm.workflow_analytic_instance_id = 204
AND EXISTS ( SELECT *
    FROM track_message_to_track_mapping tm2tm
    JOIN track t ON t.id = tm2tm.track_id
    JOIN track_item ti ON t.id = ti.track_id
    JOIN track_point tp ON ti.id = tp.track_item_id
    WHERE tm.id =tm2tm.track_message_id
    AND ti.item_time BETWEEN 1328816277089000 AND 1328816287089000
    AND ST_Intersects
            (tp.track_position
            , ST_GeomFromText('POLYGON((-144 59, -41 46, -75 15, -127 25, -144 59))',4326)
            )
    )
    ;

Here is my function

CREATE OR REPLACE Function getTrackMessages(workflow bigint, start_time bigint, end_time bigint) returns text[]
as $$
SELECT DISTINCT tm.proto_location
FROM track_message tm
WHERE tm.workflow_analytic_instance_id = $1AND EXISTS ( SELECT *
    FROM track_message_to_track_mapping tm2tm
    JOIN track t ON t.id = tm2tm.track_id
    JOIN track_item ti ON t.id = ti.track_id
    JOIN track_point tp ON ti.id = tp.track_item_id
    WHERE tm.id =tm2tm.track_message_id
    AND ti.item_time BETWEEN $2 AND $3        AND ST_Intersects
            (tp.track_position
            , ST_GeomFromText('POLYGON((-144 59, -41 46, -75 15, -127 25, -144 59))',4326)
            )
    )
    ;
$$ Language 'plpgsql';

I keep getting an error saying Syntax error at or near "select" and its refering to the line

SELECT DISTINCT tm.proto_location

Erwin Brandstetter · Accepted Answer · 2022-06-14T21:16:33.343

CREATE OR REPLACE FUNCTION get_track_messages(workflow bigint, start_time bigint, end_time bigint)
  RETURNS SETOF text
  LANGUAGE sql AS
$func$
SELECT DISTINCT tm.proto_location
FROM   track_message tm
WHERE  tm.workflow_analytic_instance_id = $1
AND    EXISTS (
    SELECT FROM track_message_to_track_mapping tm2tm
    JOIN   track        t  ON t.id = tm2tm.track_id
    JOIN   track_item   ti ON t.id = ti.track_id
    JOIN   track_point  tp ON ti.id = tp.track_item_id
    WHERE  tm.id = tm2tm.track_message_id
    AND    ti.item_time BETWEEN $2 AND $3
    AND    ST_Intersects (tp.track_position
                        , ST_GeomFromText('POLYGON((-144 59, -41 46, -75 15, -127 25, -144 59))',4326))
    );
$func$;

Most importantly, it would work as sql function.
For a PL/pgSQL function you'd need to change more:

CREATE OR REPLACE FUNCTION get_track_messages(workflow bigint
                                            , start_time bigint
                                            , end_time bigint)
  RETURNS SETOF text
  LANGUAGE plpqsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT DISTINCT ... ;
END
$func$;

If the query returns more than 1 row, you also need SETOF (as you figured out yourself). Call the function with:

SELECT * FROM get_track_messages( ... );

More about returning from a function in the manual.

Data type

proto_location is a character type. If you actually want to return an array of text (like your title states) you can aggregate like this:

SELECT array_agg(DISTINCT tm.proto_location) ...

And adapt the return type of the function to SETOF text[].

For a sorted array:

SELECT array_agg(DISTINCT tm.proto_location ORDER BY proto_location) ...

Or, to get a sorted list (text) instead of an array (text[]):

SELECT string_agg(DISTINCT tm.proto_location ORDER BY proto_location, ', ') ...

See:

I tried the sql one and got an error saying return type mismatch in function declared to return text[]. Actual return type is text. My query when just run as a query returns tons of records of type text. I changed the returns text[] to just text and it works but thats not what i'm looking for. I want a list of all of the results. I also tried the PL/pgSQL one and had issues. Got same result as my original. For the lines return QUERY and SELECT DISTINCT ...; Should I put return query there or is that where my query goes and do I have to put it in () — Jeremy, Mar 04 '14 at 13:46
@Jeremy: Without table definition in your question, how can I guess your column types? I added some pointers. Also for returning from a function. You can also find many examples [in the `[plpgsql]` tag](http://stackoverflow.com/search?q=[plpgsql]+%22RETURN+QUERY%22). — Erwin Brandstetter, Mar 04 '14 at 16:10
I assumed that by specifying that my return type was text that it meant that the data being returned was of type text I'm sorry. I did however manage to get my original query to work changing the returns text[] as $$ to returns setof text as $$ . Also had to change the language to sql. — Jeremy, Mar 04 '14 at 22:56
@Jeremy: Oh, of course. `SETOF` was my oversight, sorry. That should have been clear from the beginning. Amended the answer. — Erwin Brandstetter, Mar 04 '14 at 22:59
The solution was a setof text not setof text[] but thanks for your help. — Jeremy, Mar 05 '14 at 13:56

Create function that returns an array of text

1 Answers1

Data type