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;

What about creating a temp table? create temp tables v as select ...; then run select count(*) from v; but I don't understand why you can't run select count(*) from (select ...) v; directly? — , Jan 30 '23 at 08:05
I had the assumption that PLPGSQL would be some kind of scripting language and I could use it as a REPL environment, but that does not seem to be the case.
I can run it directly, but if I'm often changing the 'main' query that the others depend on (like the SELECT COUNT(*) ... it gets a bit cumbersome (text manipulation + number of button presses) having to copy/paste and rerun each query. — Chris Stryczynski, Jan 30 '23 at 10:53
PL/pgSQL is a (procedural) programming language to write stored procedures and function. Maybe CTEs can help. — , Jan 30 '23 at 10:54
As for temporary tables - that probably will be helpful to me. Thank you. — Chris Stryczynski, Jan 30 '23 at 10:55

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

Sorry my question likely was not very clear. I think it's just from my misunderstanding of how PLPGSQL can be used... But I was looking to run both the query and the count (as opposed to only the count) in basically one step. — Chris Stryczynski, Feb 02 '23 at 09:12

A simple example of how to use PLPGSQL?

1 Answers1

For the given example

Execute a query and get the result count