Postgres and alias

Hmm, how about:

SELECT substr (ls_number, 3, 3)
FROM (VALUES('1234567890')) as t(ls_number);

St.

Hi Daniel,

On 27. Aug, 2020, at 17:55, Fontana Daniel C (Desartec S.R.L.) <desartecsrl@gmail.com> wrote:

Hi, I am migrating a database from Sybase to PostgreSql 12.

This select in sybase works for me, but with postgresql it accuses me
"THERE IS NO COLUMN ls_numero"

select '1234567890' as ls_number,
substr (ls_number, 3, 3);

Is it possible to get information from an alias in postgresql? how would the
code be?

try this:

postgres=# select ls_number, substr (ls_number, 3, 3) from (select '1234567890' as ls_number) a;
ls_number | substr
------------+--------
1234567890 | 345
(1 row)

Cheers,
Paul

Perfect.

now let's imagine that '1234567890' is a function f_art_get_price(id_code),
which returns in a string like the following 'XXXZMMM1234567890123yyyy/mm/dd'
where 1234567890123 is the price and yyyy/mm/dd the date it was last changed price.
How would you do in this case to obtain these values ​​separately?
without calling the function 2 times avoiding overloading the base?

something like this

select art.description,
f_art_get_price_str( art.id ) as ls_price_and_date
SUBSTRING( ls_price_and_date, 7, 13 )
from articulos;

-----Mensaje original-----
De: Stelios Sfakianakis [mailto:sgsfak@gmail.com]
Enviado el: jueves, 27 de agosto de 2020 12:59
Para: Fontana Daniel C (Desartec S.R.L.)
CC: pgsql-general@lists.postgresql.org
Asunto: Re: Postgres and alias

Hmm, how about:

SELECT substr (ls_number, 3, 3)
FROM (VALUES('1234567890')) as t(ls_number);

St.

On 28 Aug 2020, at 2:14, Fontana Daniel C (Desartec S.R.L.) <desartecsrl@gmail.com> wrote:

Perfect.

now let's imagine that '1234567890' is a function f_art_get_price(id_code),
which returns in a string like the following 'XXXZMMM1234567890123yyyy/mm/dd'
where 1234567890123 is the price and yyyy/mm/dd the date it was last changed price.
How would you do in this case to obtain these values ​​separately?
without calling the function 2 times avoiding overloading the base?

something like this

select art.description,
f_art_get_price_str( art.id ) as ls_price_and_date
SUBSTRING( ls_price_and_date, 7, 13 )
from articulos;

Let's assume art is supposed to be an alias for articulos.
Something like this?:

select art.description,
p.ls_price_and_date,
SUBSTRING( p.ls_price_and_date, 7, 13 )
from articulos art
cross join lateral f_art_get_price_str( art.id ) p(ls_price_and_date);

Alban Hertroys
--
There is always an exception to always.

Hi,

Either:

SELECT description, SUBSTRING( ls_price_and_date, 8, 13) price, SUBSTRING( ls_price_and_date, 21) date
FROM (
SELECT art.description, f_art_get_price_str( art.id ) ls_price_and_date
FROM articulos art
) t;

Or use a CTE <https://www.postgresql.org/docs/12/queries-with.html&gt; (which is the exact same thing <https://paquier.xyz/postgresql-2/postgres-12-with-materialize/&gt;):

WITH t AS (
SELECT art.description, f_art_get_price_str( art.id ) ls_price_and_date
FROM articulos art
)
SELECT description, SUBSTRING( ls_price_and_date, 8, 13) price, SUBSTRING( ls_price_and_date, 21) date
FROM t;

Best,
Stelios

On Thu, 2020-08-27 at 12:55 -0300, Fontana Daniel C (Desartec S.R.L.) wrote:

Hi, I am migrating a database from Sybase to PostgreSql 12.

This select in sybase works for me, but with postgresql it accuses me
"THERE IS NO COLUMN ls_numero"

select '1234567890' as ls_number,
substr (ls_number, 3, 3);

Is it possible to get information from an alias in postgresql? how would the
code be?

You have several options:

1. Repeat the literal:

select '1234567890' as ls_number,
substr ('1234567890', 3, 3);

2. Use a subquery:

select ls_number,
substr (ls_number, 3, 3)
FROM (SELECT '1234567890' AS ls_number) AS q;

3. Use a CTE:

WITH x AS (SELECT '1234567890' as ls_number)
SELECT ls_number,
substr (ls_number, 3, 3)
FROM x;

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com