Execute a SELECT with dynamic ORDER BY expression inside a function

Question

I'm trying to EXECUTE some SELECTs to use inside a function, my code is something like this:

DECLARE
result_one record;

BEGIN 
    EXECUTE 'WITH Q1 AS 
        (
            SELECT id
            FROM table_two
            INNER JOINs, WHERE, etc, ORDER BY... DESC
        )

        SELECT Q1.id
        FROM Q1 
        WHERE, ORDER BY...DESC';

RETURN final_result;
END;

I know how to do it in MySQL, but in PostgreSQL I'm failing. What should I change or how should I do it?

It's not obvious (to me at least) why you are using `EXECUTE`, or what results you expect from this query. There's no reason for the above code to even be in plpgsql - you could just use a SQL function — Richard Huxton, Nov 12 '18 at 15:28
I know, the selects are examples, I need to know how to use the execute in this case. Those select has dynamic parameters, and some people will edit the parameters through CLI, long story. — pmiranda, Nov 12 '18 at 15:30
Then you will need to give a more accurate example if you want people to figure out what your problem is. That code just isn't returning any results and I can't see why you would expect it to. You have read the relevant part of the manuals (https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) haven't you? — Richard Huxton, Nov 12 '18 at 15:43
Note that *functions* are distinct from *procedures*, so "stored procedure" (SP) is a misleading term when referring to a function. See: https://dba.stackexchange.com/a/194811/3684 — Erwin Brandstetter, Nov 13 '18 at 22:50

score 1 · Answer 1 · answered Nov 12 '18 at 15:45

For a function to be able to return multiple rows it has to be declared as returns table() (or returns setof)

And to actually return a result from within a PL/pgSQL function you need to use return query (as documented in the manual)

To build dynamic SQL in Postgres it is highly recommended to use the format() function to properly deal with identifiers (and to make the source easier to read).

So you need something like:

create or replace function get_data(p_sort_column text)
  returns table (id integer)
as
$$
begin
  return query execute 
    format(
     'with q1 as (
           select id
           from table_two
             join table_three on ...
         )
         select q1.id
         from q1
         order by %I desc', p_sort_column);
end;
$$
language plpgsql;

Note that the order by inside the CTE is pretty much useless if you are sorting the final query unless you use a LIMIT or distinct on () inside the query.

You can make your life even easier if you use another level of dollar quoting for the dynamic SQL:

create or replace function get_data(p_sort_column text)
  returns table (id integer)
as
$$
begin
  return query execute 
    format(
     $query$ 
       with q1 as (
           select id
           from table_two
             join table_three on ...
         )
         select q1.id
         from q1
         order by %I desc
     $query$, p_sort_column);
end;
$$
language plpgsql;

Erwin Brandstetter · Answer 2 · 2018-11-17T17:13:09.227

What a_horse said. And:

How to return result of a SELECT inside a function in PostgreSQL?

Plus, to pick a column for ORDER BY dynamically, you have to add that column to the SELECT list of your CTE, which leads to complications if the column can be duplicated (like with passing 'id') ...

Better yet, remove the CTE entirely. There is nothing in your question to warrant its use anyway. (Only use CTEs when needed in Postgres, they are typically slower than equivalent subqueries or simple queries.)

CREATE OR REPLACE FUNCTION get_data(p_sort_column text)
  RETURNS TABLE (id integer) AS
$func$
BEGIN
RETURN QUERY EXECUTE format(
   $q$ 
   SELECT t2.id  -- assuming you meant t2?
   FROM   table_two   t2
   JOIN   table_three t3 on ...
   ORDER  BY t2.%I DESC NULL LAST  -- see below!
   $q$, $1);
END
$func$  LANGUAGE plpgsql;

I appended NULLS LAST - you'll probably want that, too:

PostgreSQL sort by datetime asc, null first?

If p_sort_column is from the same table all the time, hard-code that table name / alias in the ORDER BY clause. Else, pass the table name / alias separately and auto-quote them separately to be safe:

Define table and column names as arguments in a plpgsql function?

I suggest to table-qualify all column names in a bigger query with multiple joins (t2.id not just id). Avoids various kinds of surprising results / confusion / abuse.

And you may want to schema-qualify your table names (myschema.table_two) to avoid similar troubles when calling the function with a different search_path:

How does the search_path influence identifier resolution and the "current schema"

Execute a SELECT with dynamic ORDER BY expression inside a function

2 Answers2