Thread: 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;

Edson F. Lidorio <edson@openmailbox.org> wrote:

> Hello,
> I excuse my ignorance with SQL and my English.
> I wonder if these procedures are correct or is it a bug?

It's not a bug, but storing the result in a new table is senseless. Why
do are doing that?



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Edson F. Lidorio
Sent: Freitag, 25. Dezember 2015 14:23
To: pgsql-general@postgresql.org
Subject: [GENERAL] 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

 

via pgadmin not accept this syntax.
You have any other way to do?

On 12/25/2015 08:37 AM, Melvin Davidson wrote:
> FYI, it is always wise (and polite) to advise what version of PostgreSQL
> you are using and what O/S you are using.

Actually the OP put that at the bottom of the first post:

"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
"

> It would also be nice to know exactly what you are trying to do. IE:
> What is your use case?
>



--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/25/2015 08:26 AM, Edson F. Lidorio wrote:
>
>
> On 25-12-2015 13:09, Charles Clavadetscher wrote:
>>
>> 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

>> Edson
>>
> via pgadmin not accept this syntax.

That is because \set is unique to the psql interactive client and the
pgAdmin SQL Query tool does not understand it.

> You have any other way to do?

Yes inside a function. I suspect that is where you got this:

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

Someone posted something similar from a plpgsql function. To make things
confusing the above is a way you can assign output into a variable in
plpgsql. For the details see:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

in particular:

"Tip: Note that this interpretation of SELECT with INTO is quite
different from PostgreSQL's regular SELECT INTO command, wherein the
INTO target is a newly created table. If you want to create a table from
a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE
... AS SELECT."



--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/25/2015 08:26 AM, Edson F. Lidorio wrote:
>
>

>>
>>
>> I'musing versionbelowPostgreSQL.
>>
>> PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
>> 4.9.2-10) 4.9.2, 64-bit
>>
>> --
>> Edson
>>
> via pgadmin not accept this syntax.
> You have any other way to do?

Should have added to previous post that this can be done using an
anonymous function:

http://www.postgresql.org/docs/9.4/interactive/sql-do.html

The caveat being anonymous functions cannot return anything. Still
something like the below:

DO
$$
DECLARE
     var_1 integer;
BEGIN
     SELECT INTO var_1 sum(cell_per) FROM cell_per;
     RAISE NOTICE 'Sum is %', var_1;
END
$$ LANGUAGE plpgsql;

NOTICE:  Sum is 193



--
Adrian Klaver
adrian.klaver@aklaver.com