CODEWITHSUNDEEP
postgresqlplpgsqlcommon-table-expressiondynamic-sqlstored-functions
pmiranda
pmiranda

Reputation: 8460

Execute a SELECT with dynamic ORDER BY expression inside a function

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?

Upvotes: 0

Views: 1938

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658472

What a_horse said. And:

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:

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:

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:

Upvotes: 0

user330315
user330315

Reputation:

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;

Upvotes: 1

Related Questions