Re: Strange syntax with select

["Charles Clavadetscher" <clavadetscher@xxxxxxxxxxxx>]

Hello

 

I am not in clear what your use case is, but you may have a look at that:

 

http://www.depesz.com/2013/02/25/variables-in-sql-what-how-when/

 

The bottom line is that in a psql interactive session you cannot really set a variable as the result of a select statement (or at least I did not fine a way). Instead, depending on what you actually want to achieve, you may use a workaround storing a statement or part of it in a variable.

 

Here an example:

db=> create table test (id int);

CREATE TABLE

db => insert into test select generate_series(1,10);

INSERT 0 10

db => \set testvar 'sum(id) from test'

db => select :testvar;

sum

-----

 55

(1 row)

 

Bye

Charles

 

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Edson F. Lidorio
Sent: Freitag, 25. Dezember 2015 14:23
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Strange syntax with select

 

Hello,
I excuse my ignorance with SQL and my English.
I wonder if these procedures are correct or is it a bug?
I'm trying to put the result of a select within the v_saldo_ini variable, except I realized that the postgresql created a table with v_saldo_ini variable.

See the steps below:

CREATE TABLE contas
(
   vlr_saldo_inicial numeric(14,2)) ;

select sum(vlr_saldo_inicial) as saldo_ini
into v_saldo_ini
from contas;

--postgresql created v_saldo_ini table

select * from v_saldo_ini;
 saldo_ini
-----------
  20000.00
(1 record)


--if I executat = ro select below, returns the table v_saldo_ini already exists

select sum(vlr_saldo_inicial) as saldo_ini
 into v_saldo_ini
from contas;

ERROR: relation "v_saldo_ini" already exists


I'm using version below PostgreSQL.

PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

--
Edson