Re: Strange syntax with select

[Melvin Davidson <melvin6925@xxxxxxxxx>]

FYI, it is always wise (and polite) to advise what version of PostgreSQL you are using and what O/S you are using.

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

That being said, you can assign a result of an _expression_ to a variable in a PostgreSQL function http://www.postgresql.org/docs/9.4/interactive/plpgsql.html

Or, you can use bash (or some other command language to do the same.

But without you telling us Exactly what you are trying to do, and Why, we cannot advise much further.

On Fri, Dec 25, 2015 at 11:26 AM, Edson F. Lidorio <edson@xxxxxxxxxxxxxxx> 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

 

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

 

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

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.