A simple example of how to use PLPGSQL?

Question

How can I define a query, and then run the query and a SELECT COUNT(*) (return the number of records) on that query's result?

I want results similar to:

postgres@127:postgres> (SELECT 1) union all (select 2) union all (select 3);
+----------+
| ?column? |
|----------|
| 1        |
| 2        |
| 3        |
+----------+
SELECT 3
Time: 0.049s
postgres@127:postgres> select count(*) from ((SELECT 1) union all (select 2) union
  all (select 3)) as q;
+-------+
| count |
|-------|
| 3     |
+-------+
SELECT 1

It must be a sql statement as I'm running this via pgcli which does not support terminal variables (https://github.com/dbcli/pgcli/issues/829).

I'm trying to use an anonymous PLPGSQL function but struggling to figure it out... And there are multitude of things I can look into next (anonymous code block, using non-anonymous function but then deleting it afterwards, return results from functions (a query instead of a set or rows?)) but I don't know what to focus on. I have this so far:

DO $$DECLARE v record;
BEGIN
  SELECT 10,20,30 into v;
END$$;
SELECT COUNT(*) FROM v;

Erwin Brandstetter · Accepted Answer · 2023-02-02T23:58:14.333

For the given example

You shouldn't need PL/pgSQL, nor temp tables, nor CTEs for the task.
While combining queries with UNION ALL like in your example:

select count(*) from ((SELECT 1) union all (select 2) union all (select 3)) as q;

This is a much cheaper equivalent:

SELECT (SELECT count(*) FROM <rest of qry1 here>)
     + (SELECT count(*) FROM <rest of qry2 here>)
     + (SELECT count(*) FROM <rest of qry3 here>) AS q;

Since count(*) never returns NULL, this is also NULL-safe.

If you cannot change the given SELECT queries at all, you can encapsulate them in subqueries:

SELECT (SELECT count(*) FROM (SELECT 1) q)
     + (SELECT count(*) FROM (SELECT 2) q)
     + (SELECT count(*) FROM (SELECT 3) q) AS q;

Still (much) cheaper than building the whole result set before counting. (Just to throw it all away afterwards.)

Execute a query and get the result count

Use GET DIAGNOSTICS in PL/pgSQL. Example:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS SETOF int
  LANGUAGE plpgsql AS
$func$
DECLARE
   ct int;
BEGIN
   RETURN QUERY
   (SELECT 1) UNION ALL (SELECT 2) UNION ALL (SELECT 3);
   GET DIAGNOSTICS ct = ROW_COUNT;
RAISE NOTICE 'Returned % rows', ct;
END
$func$;

Call:

SELECT * FROM f_test();

See:

How to access the number of rows accumulated by RETURNs in PL/pgSQL

A simple example of how to use PLPGSQL?

1 Answers1

For the given example

Execute a query and get the result count