Any tool to run an SQL query (against postgres) with :parameters without manually replacing them?

Question

Lets say I have a query

select * from orders   where company=:company

And I run it by setting parameter in code.

All well and good, but what if I want to run this query in my sql tool (pgadmin for example) but I do not want to manually replace parameters with actual values (the real query has 5 paramters and is very big).

Is there a tool or a way to run the query and set parameters values somewhere so they would be used in query as if it was run from code?

If it is relevant I use C# and NHibernate (but in this case I use native SQL query) and Postgresql.

EDIT: Requirement: SQL query text should not be changed in any way, paramater should be specified with ':' character not any other way. And it should not be a command line tool (but something with visual UI).

possible duplicate of [How do you use variables in a simple PostgreSQL script?](http://stackoverflow.com/questions/766657/how-do-you-use-variables-in-a-simple-postgresql-script) — Jakub Kania, Sep 25 '14 at 13:11
OK, I tried \set name=value example, cannot get it to work in pgadmin, I guess I am missing something? — deafsheep, Sep 25 '14 at 13:29
Thanks you for clarification. So is there a way to do what I asked from any visual sql tool (not pgadmin specifically any will do) — deafsheep, Sep 25 '14 at 13:35
The first comment in that question suggests using DO for inline functions. Exactly what you need. — Jakub Kania, Sep 25 '14 at 13:47
@Jakub That answer is not adequate since an anonymous code block does not return anything other than `void`. I wonder how did that answer get so many votes. — Clodoaldo Neto, Sep 25 '14 at 13:50
Of course they can return things, you just have to be creative about it. — Jakub Kania, Sep 25 '14 at 15:19

Clodoaldo Neto · Answer 1 · 2014-09-25T13:57:19.470

0

The easiest is to wrap the query in a function

create function f(_company text)
returns setof orders as $$
    select *
    from orders
    where company = _company
;
$$ language sql;

Then use it like this in the graphical client

select * from f('company name');

And in the C# client use it with the same parameter syntax:

select * from f(:company);

The use of functions will simplify your code. They are reusable. There is no need to rebuild the application if only the function was changed.

edited Sep 25 '14 at 13:57

answered Sep 25 '14 at 13:36

Clodoaldo Neto

118,695
26
233
260

Maybe I didnt explain my question good enough. But what I want to do is not to change the syntax of a sql query at all (leave parameter as :company) and run it like that. It is just a productivity thing, for saving time, not actual programming task. I just thought that maybe there is some tool that can do that out of the box. – deafsheep Sep 25 '14 at 13:42
@deafsheep I understand that. The `create function` code will not be in your code. Just the `select`. Check the update. – Clodoaldo Neto Sep 25 '14 at 13:49
This is an interesting variation and it technically fits what I asked, but you need to create function on server, which is an additional requirement. Thanks you very much for your answer but I was looking for more of "UI" way of doing this. Maybe the question is too vague and I should close it... – deafsheep Sep 25 '14 at 13:58

score 0 · Answer 2 · answered Sep 25 '14 at 15:18

You created a problem that doesn't really exist. If you really must use something diffrent for writing your query then anonymous blocks are the best solution:

DROP TABLE IF EXISTS foobar;
DO $$
DECLARE _foo TEXT;
DECLARE _bar TEXT;
BEGIN
  _foo := 'foo' ;
  _bar := 'bar' ;
  CREATE TEMP TABLE foobar AS SELECT _foo, _bar;
END $$;
SELECT * FROM foobar;

You get 5 additional lines + 2 lines for each variable. When you're happy just do a find and replace of " _" into " :" and you will have your query.

Any tool to run an SQL query (against postgres) with :parameters without manually replacing them?

2 Answers2