0

I am building a query for a report and it has a lot of repeated const values in calculated columns. Simplified example:

select
    c.title || ' corp', -- more than one column appending this
    to_char(c.last_contact, 'YYYY-MM-DD HH24:MI') -- this format is used for other columns as well
from clients c;

I want to extract those repeated const values into variables and use variables instead. In TSQL I could achieve it with code like this:

declare
    @suffix varchar(8) = ' corp',
    @dateFormat varchar(16) = 'YYYY-MM-DD HH24:MI' -- let's pretend that's a correct format string for TSQL

select
    c.title + @suffix,
    convert(varchar(32), c.last_contact, @dateFormat)
from clients c

I'm trying to achieve same behavior in pl/pgsql with this:

do $$
declare
    suffix text := ' corp';
    date_format text := 'YYYY-MM-DD HH24:MI';
begin
    select
        c.title || suffix,
        to_char(c.last_contact, date_format)
    from clients c;
end;
$$;

And it breaks:

ERROR: query has no destination for result data
  Hint: If you want to discard the results of a SELECT, use PERFORM instead.
  Where: PL/pgSQL function inline_code_block line 5 at SQL statement
1 statement failed.

So far I've only found examples of using variables in WHERE block, but none in SELECT block.

I've seen EXECUTE 'select ...' WITH examples, but my quick attempt of using that

do $$
declare
    process_start timestamp := now();
begin
    execute 'select $1;'    
    using process_start;
end;
$$;

just reports DO executed successfully without actually selecting anything.

Is there a way to use pl/pgsql variables in calculated columns?

Any other alternatives to achieve this in postgres?

Justinas Marozas
  • 2,482
  • 1
  • 17
  • 37
  • What do you want to do with that value? You have to store it somewhere. Your example doesn't make sense to me. Why PL/pgSQL? Why not simply use `now()` in your query? –  Feb 21 '18 at 17:28
  • @a_horse_with_no_name I used `now()` as an example, because I need process start date as one of the columns. The process takes more than a second to run and I don't expect `now()` to be same value as the moment the query begun. I also have text constants that need appending to some columns. – Justinas Marozas Feb 21 '18 at 17:39
  • You need to explain the underlying problem, not the "solution" you think you need. Also: `now()` doesn't change during a transaction: https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT –  Feb 21 '18 at 17:54
  • @a_horse_with_no_name tank you for clarifying `now()` behavior. Please see updated question. – Justinas Marozas Feb 21 '18 at 18:32
  • @a_horse_with_no_name , can you explain how does https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query answer my question? I don't have problems declaring variables or printing their values. I have problems with using them in calculated columns. – Justinas Marozas Feb 21 '18 at 22:57
  • You can not use variables in SQL queries, simple as that. And you can't have a `do` block that returns a result. The duplicate questions shows you various workarounds for using variables in queries as you are doing in the T-SQL example. PL/pgSQL is **not** a replacement for that if you want to run queries (using variables) and **see** their result. –  Feb 21 '18 at 23:21

2 Answers2

0

What do you want to do with this value? Puzzling example. Using a FUNCTION it could be solved more or less like this 

create or replace function my_function()
returns timestamp as $$
declare
    process_start timestamp := now();
begin
    return process_start;
end;
$$
language plpgsql

If you really want to ignore the result of the only select in your code, you can use PERFORM

do $$
declare
    process_start timestamp := now();
begin
    perform (select process_start);
end;
$$;
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
0

To use SELECT in PL/pgSQL, you have to specify an INTO clause:

SELECT process_start, s.title || suffix
   INTO var1, var2
   FROM ...
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263