Re: Postgres and alias

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> (which is the exact same thing <https://paquier.xyz/postgresql-2/postgres-12-with-materialize/>):

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 28 Aug 2020, at 03:14, Fontana Daniel C (Desartec S.R.L.) <desartecsrl(at)gmail(dot)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;
>
> -----Mensaje original-----
> De: Stelios Sfakianakis [mailto:sgsfak(at)gmail(dot)com]
> Enviado el: jueves, 27 de agosto de 2020 12:59
> Para: Fontana Daniel C (Desartec S.R.L.)
> CC: pgsql-general(at)lists(dot)postgresql(dot)org
> Asunto: Re: Postgres and alias
>
> Hmm, how about:
>
> SELECT substr (ls_number, 3, 3)
> FROM (VALUES('1234567890')) as t(ls_number);
>
> St.
>
>> On 27 Aug 2020, at 18:55, Fontana Daniel C (Desartec S.R.L.) <desartecsrl(at)gmail(dot)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?
>>
>>
>>
>>
>