CODEWITHSUNDEEP
postgresql
Peter Wone
Peter Wone

Reputation: 18755

Return variable in postgres select

This covers most use cases How do you use variables in a simple PostgreSQL script? but not the select clause.

This code produces an error column "ct" does not exist"

DO
$$

declare CT timestamp := '2020-09-04 23:59:59';

select CT,5 from job;

$$;

I can see why it would interpret CT as a column name. What's the Postgres syntax required to refer to a variable in the context of the select clause?

I would expect that query to return

'2020-09-04 23:59:59',5

for each row in the job table.

Addendum to the accepted answer

My use case doesn't return rows. Instead, the result of the select is consumed by an insert statement. I'm transforming rows from staging tables into other tables and adding value like the import date and the identity owning the inserts. It's these values that are provided by the variables - they are used in several such transforms and the point of the variable is to let me set each value once up the top of the script.

Because the rows are consumed like this, it turns out that I don't need a function wrapping this code. It's a bit inconvenient to test since I can't run the select and look at the outcome without copying it and pasting in literals, but at least it's possible to use variables. My working script looks like this:

do
$$
declare ct timestamp := '2020-09-04 23:59:59';
declare cb int := 2;
declare iso8601 varchar(50) := 'YYYY-MM-DD HH24:MI:SS';
declare USAdate varchar(50) := 'MM-DD-YYYY HH24:MI:SS';
begin

delete from dozer_wheel_loader_equipment_movement where created = ct;
INSERT INTO dozer_wheel_loader_equipment_movement 
(site, primary_category_id, machine, machine_class, x, y, z, timestamp_local, created, created_by)
select site ,mc.id ,machine , machineclass ,x,y,z,to_timestamp(timestamplocal, iso8601), ct, cb 
from stage_dozer_csv d join machine_category mc on d.primarycategory = mc.short_code;
...
end
$$

There is a lot of worthwhile related reading at How to declare a variable in a PostgreSQL query

Upvotes: 1

Views: 3712

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

There are few things about variables in PostgreSQL.

Variable can not be used in Plain SQL in Postgres. So you have to use any pl language i.e. plpgsql to use this. You have tried the same in your example.

In your DO block you have missed the Begin and End, So you have to write it like below

DO
$$
begin
declare CT timestamp := '2020-09-04 23:59:59';
select CT,5 from job;
end
$$;

But when you read the official documentation of DO Statement, it says DO will allow to run the anonymous code but it returns void, that's why above code will throw following error:

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

So there is only one way - wrap this code block in a Function like below:

create or replace function func() returns table(col1 timestamp, col2 int )
AS
$$
declare ct timestamp := '2020-09-04 23:59:59';
begin
return query
select CT,5 from job;
end;
$$
language plpgsql

and you can call it like below:

select * from func()

DEMO

Conclusion

  1. You can not use variable in normal SQL statement in Postgres.
  2. You have to use any Procedural Language i.e. plpgsql to use variable.
  3. DO Block doesn't return any value so you can not use select statement like above in DO block. It is good for non-returning queries i.e. insert, update, delete or grant etc.
  4. Only way to return a value from procedural language code block is - you have to wrap it in a suitable PostgreSQL Function.

Upvotes: 1

Related Questions