Duplicate the result of Select Postgresql

2

I have a record that is a receipt, and in select, I want the line to be duplicated. Ex:

Select * from recibos where codigo =1;

Result:

codigo|cliente|valor|emitente|data
  1    Fulano   10    Ciclano  19/10/2017

Result I need:

codigo|cliente|valor|emitente | data       |  via
  1    Fulano   10    Ciclano   19/10/2017     1
  1    Fulano   10    Ciclano   19/10/2017     2
    
asked by anonymous 19.10.2017 / 18:44

2 answers

1

Assuming that:

CREATE TABLE tb_recibos (
    codigo integer,
    cliente text,
    valor integer,
    emitente text,
    data date
);

INSERT INTO tb_recibos ( codigo, cliente, valor, emitente, data )
VALUES ( 1, 'Fulano', 10, 'Ciclano', '2017.10.19' );

Solution # 1 (Less Compact, Complex Plane) :

SELECT
    r.codigo,
    r.cliente,
    r.valor,
    r.emitente,
    r.data,
    s AS via
FROM
    tb_recibos AS r 
CROSS JOIN
    generate_series( 1, 2 ) AS s
WHERE
    codigo = 1;

Solution # 2 (More compact, plain plan) :

SELECT
    r.codigo,
    r.cliente,
    r.valor,
    r.emitente,
    r.data,
    generate_series( 1, 2 ) as via
FROM
    tb_recibos AS r 
WHERE
    codigo = 1;

See working in SQLFiddle

    
19.10.2017 / 21:02
1

I got the result expecting using the following syntax:

 Select 
     r.*,
     s.via 
 from recibos r 
 inner join generate_series(1,2) s(via) on 1=1
 where codigo = 1;

If someone has another suggestion of how to get the same result, thank you.

    
19.10.2017 / 18:44