0

I have seen many explanations about SELECT INTO in the oracle clause. I found a difference in applying SELECT INTO in Oracle and other SQL databases. In Oracle, the SELECT INTO statement retrieves values from one or more database tables (as the SQL SELECT statement does) and stores them in variables (which the SQL SELECT statement does not do). Whereas in Postgres, SELECT INTO creates a new table from the results of a query.

I want to apply/implement such a thing in Postgres. What is the appropriate command or statement there?

Here is my Oracle query:

SELECT COUNT(1)
INTO ada
FROM atk_history_qty
WHERE tgl BETWEEN TO_DATE ('2014/02/01', 'yyyy/mm/dd')
AND TO_DATE ('2014/02/28', 'yyyy/mm/dd');
devins10
  • 1
  • 2
    Select INTO is not "SQL" it's PL/SQL and PostgreSQL's PL/pgSQL supports the same thing. So if you are converting a stored procedure or function from PL/SQL to PL/pgSQL, this should be pretty much the same syntax. Btw: it's an urban myth that count(1) is faster than count(*). In fact in Postgres count(1) is actually slightly slower. –  Jan 17 '23 at 10:40
  • count(1) is fractionally slower in Oracle as well, as the parser has to convert it into count(*) and that has to take a couple of cpu cycles. – William Robertson Jan 22 '23 at 19:26

2 Answers2

1

There are no variables in PostgreSQL's SQL dialect (nor are there in the SQL standard). If you are using psql as a client, you can use psql variables with \gset:

test=> SELECT count(*) AS myvar FROM pg_class \gset
test=> \echo :myvar
580

\gset creates variables with the same name as the aliases in the SELECT list.

Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69
0

"Customized options" have been extended to be usable for this purpose:

SELECT set_config('myvar.ada', count(*)::text, false)
FROM   atk_history_qty
WHERE  tgl BETWEEN date '2014-02-01' AND date '2014-02-28';

Then:

SELECT current_setting('myvar.ada')::bigint;

The manual:

set_config ( setting_name text, new_value text, is_local boolean ) → text

Sets the parameter setting_name to new_value, and returns that value. If is_local is true, the new value will only apply during the current transaction. If you want the new value to apply for the rest of the current session, use false instead. This function corresponds to the SQL command SET.

There are some limitations:

  • Only strings (type text) are stored and retrieved. You may have to cast the type back and forth. (Like demonstrated in the example.)
  • To set user-options apart from system parameters, the name must be qualified with an extension name, like myvar.ada.

Asides:

count(*) is always preferable to count(1). It's equivalent, but a bit faster.
Dates in ISO format are always unambiguous, so we don't need to_date() for this input. If tgl is type date, the plain literal '2014-02-01' does it, too.

There are various other options to store variables, depending on context and scope:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600