0

I am trying to run a query in a PostgreSQL client (Postico) and an experiencing nothing but frustration for what I assumed would be a fairly straightforward task, as it is in T-SQL.

My declarations are as follows:

DECLARE vYOU varchar := 'LD', 
vSOME_ID int := '9999', 
vSOME_THING varchar := 'ABCD.0.1.2', 
vSOME_THING2 varchar := 'ABCD.0.0.2', 
vSTART_DATE date := '2016-08-23', 
vEND_DATE date := '2016-08-24';

The variables are prepended with 'v' and are of course referenced at various points throughout the remainder of my query.

However, this gives me the following error:

Query failed
PostgreSQL said: syntax error at or near "varchar"

I have Googled in vain, checking my datatypes and the general syntax and tried multiple iterations of this, though admittedly I'm not sure about whether I have listed the declared variables correctly. In any case I see various different answers about whether this can or can't be done in PostgreSQL.

Would love a definitive answer as to where I'm going wrong or whether this is even possible.

Thanks in advance.

jimiclapton
  • 775
  • 3
  • 14
  • 42
  • 1
    I wish it did, but no, not really... http://stackoverflow.com/questions/14652477/how-to-perform-a-select-query-in-a-do-block – Hambone Aug 24 '16 at 18:36
  • See also: [Is it possible to define global variables in postgresql](http://stackoverflow.com/q/31316053/1995738) and [Is it possible to use a variable and not specify a return type in postgreSQL?](http://stackoverflow.com/a/34143571/1995738). – klin Aug 24 '16 at 19:31
  • Thanks both. This makes it seem like it's possible, no? https://www.techonthenet.com/postgresql/declare_vars.php – jimiclapton Aug 24 '16 at 20:36
  • Within the context of a function, yes. If you want to go through the effort to build a function (that can return a query), then sure, this is quite possible. My impression was you wanted more of a stand-alone query that incorporates variables. If you want an example of the function that returns a query, that is quite doable – Hambone Aug 24 '16 at 20:57
  • @Hambone you're absolutely right, I was indeed referring to a standalone query. I have no desire at all to build a function to return this query. I didn't realise the page I linked to pertained to function creation specifically, that's not really clear. – jimiclapton Aug 24 '16 at 21:13
  • Maybe creating temporary table with keys, values and querying it using subqueries or CTE's is acceptable solution? – Jakub Fedyczak Aug 25 '16 at 08:55

0 Answers0