Thread: Junk queries with variables?

Junk queries with variables?

From

"Steve - DND"

Date:

24 February 2005, 09:12:52

I really have to be missing something here and this probably a *really* noob
question. I don't have a problem running little junk queries in the pgAdmin
query window(SELECT blah FROM blah, INSERT INTO blah, etc...), but I can't
figure out how to run queries with variables outside of a function. I just
want to use variables without having to go about creating and dropping a
function for every stupid little query I need to write. Example:

amount int4 := 1000;
earliest_date timestamp := current_timestamp;

SELECT ...

I always get the error: "ERROR:  syntax error at or near "amount" at
character 1". What have I done wrong, or am I missing?

Thanks,
Steve

Re: Junk queries with variables?

From

Michael Fuhr

Date:

24 February 2005, 10:40:55

On Wed, Feb 23, 2005 at 11:12:47PM -0700, Steve - DND wrote:

> I really have to be missing something here and this probably a *really* noob
> question. I don't have a problem running little junk queries in the pgAdmin
> query window(SELECT blah FROM blah, INSERT INTO blah, etc...), but I can't
> figure out how to run queries with variables outside of a function. I just
> want to use variables without having to go about creating and dropping a
> function for every stupid little query I need to write.

I don't know about pgAdmin, but in psql you can use \set:

\set id 1
SELECT * FROM foo WHERE id = :id;

\set name '\'Some Name\''
SELECT * FROM foo WHERE name = :name;

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Junk queries with variables?

From

Richard Huxton

Date:

24 February 2005, 11:23:46

Steve - DND wrote:
> I really have to be missing something here and this probably a *really* noob
> question. I don't have a problem running little junk queries in the pgAdmin
> query window(SELECT blah FROM blah, INSERT INTO blah, etc...), but I can't
> figure out how to run queries with variables outside of a function. I just
> want to use variables without having to go about creating and dropping a
> function for every stupid little query I need to write. Example:
> 
> amount int4 := 1000;
> earliest_date timestamp := current_timestamp;

Michael's given you one option - another to look at is PREPARE/EXECUTE
PREPARE my_query(int4) AS INSERT INTO foo VALUES ($1);
EXECUTE my_query(1);
EXECUTE my_query(7);
...

--  Richard Huxton  Archonet Ltd

Re: Junk queries with variables?

From

KÖPFERL Robert

Date:

24 February 2005, 12:09:26

In pgadmins SQL-window SQL is the 'language' of choice. Or it is rather the
only language. Thus if you intend to program plTk or PL/pgSQL, there's no
way around defining a function.

(At first you have to define a new language in your schema)

C:\> -----Original Message-----
C:\> From: Steve - DND [mailto:postgres@digitalnothing.com]
C:\> Sent: Donnerstag, 24. Februar 2005 07:13
C:\> To: pgsql-sql@postgresql.org
C:\> Subject: [SQL] Junk queries with variables?
C:\> 
C:\> 
C:\> I really have to be missing something here and this 
C:\> probably a *really* noob
C:\> question. I don't have a problem running little junk 
C:\> queries in the pgAdmin
C:\> query window(SELECT blah FROM blah, INSERT INTO blah, 
C:\> etc...), but I can't
C:\> figure out how to run queries with variables outside of a 
C:\> function. I just
C:\> want to use variables without having to go about creating 
C:\> and dropping a
C:\> function for every stupid little query I need to write. Example:
C:\> 
C:\> amount int4 := 1000;
C:\> earliest_date timestamp := current_timestamp;
C:\> 
C:\> SELECT ...
C:\> 
C:\> I always get the error: "ERROR:  syntax error at or near 
C:\> "amount" at
C:\> character 1". What have I done wrong, or am I missing?
C:\> 
C:\> Thanks,
C:\> Steve
C:\> 
C:\> 
C:\> 
C:\> ---------------------------(end of 
C:\> broadcast)---------------------------
C:\> TIP 9: the planner will ignore your desire to choose an 
C:\> index scan if your
C:\>       joining column's datatypes do not match
C:\>

Re: Junk queries with variables?

From

"Steve - DND"

Date:

24 February 2005, 20:10:39

>
> I don't know about pgAdmin, but in psql you can use \set:
>
> \set id 1
> SELECT * FROM foo WHERE id = :id;
>
> \set name '\'Some Name\''
> SELECT * FROM foo WHERE name = :name;
>

Whenever I try the above I get an error at the backslash. Do I need to
create a different language for this? Right now I only have plpgsql
available.

Thanks,
Steve

Re: Junk queries with variables?

From

"Steve - DND"

Date:

24 February 2005, 20:11:04

> Michael's given you one option - another to look at is PREPARE/EXECUTE
> PREPARE my_query(int4) AS INSERT INTO foo VALUES ($1);
> EXECUTE my_query(1);
> EXECUTE my_query(7);
> ...

This doesn't seem to be quite what I'm looking for. PREPARE according to the
docs is for a one line statement. I'm looking for the ability to do multiple
operations, basically using it for general DB queries that aren't really
needed more than once. This again comes from my MSSQL background where you
can go to town and just start writing out TSQL to do one off queries.

Thanks,
Steve

Re: Junk queries with variables?

From

Michael Fuhr

Date:

24 February 2005, 20:15:34

On Thu, Feb 24, 2005 at 10:04:50AM -0700, Steve - DND wrote:

> > \set name '\'Some Name\''
> > SELECT * FROM foo WHERE name = :name;
> 
> Whenever I try the above I get an error at the backslash. Do I need to
> create a different language for this? Right now I only have plpgsql
> available.

Did you try it in psql?  As I mentioned, \set is a psql (client side)
command.

http://www.postgresql.org/docs/8.0/static/app-psql.html

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Junk queries with variables?

From

Bricklen Anderson

Date:

24 February 2005, 20:17:38

Steve - DND wrote:
>>I don't know about pgAdmin, but in psql you can use \set:
>>
>>\set id 1
>>SELECT * FROM foo WHERE id = :id;
>>
>>\set name '\'Some Name\''
>>SELECT * FROM foo WHERE name = :name;
>>
> 
> 
> Whenever I try the above I get an error at the backslash. Do I need to
> create a different language for this? Right now I only have plpgsql
> available.
> 
> Thanks,
> Steve
>
\set name text('Some Name')
SELECT * FROM foo WHERE name = :name;

Re: Junk queries with variables?

From

Richard Huxton

Date:

24 February 2005, 20:28:57

Steve - DND wrote:
>>Michael's given you one option - another to look at is PREPARE/EXECUTE
>>PREPARE my_query(int4) AS INSERT INTO foo VALUES ($1);
>>EXECUTE my_query(1);
>>EXECUTE my_query(7);
>>...
> 
> 
> This doesn't seem to be quite what I'm looking for. PREPARE according to the
> docs is for a one line statement. I'm looking for the ability to do multiple
> operations, basically using it for general DB queries that aren't really
> needed more than once. This again comes from my MSSQL background where you
> can go to town and just start writing out TSQL to do one off queries.

Well, if psql vars, prepare or functions don't meet your needs, I'm not 
sure we've got anything that will.

Have you got a specific example where these don't suit your needs?
--  Richard Huxton  Archonet Ltd

Re: Junk queries with variables?

From

Tom Lane

Date:

24 February 2005, 20:50:29

Richard Huxton <dev@archonet.com> writes:
> Steve - DND wrote:
>> This doesn't seem to be quite what I'm looking for.

> Well, if psql vars, prepare or functions don't meet your needs, I'm not 
> sure we've got anything that will.

ecpg is another possible answer.

> Have you got a specific example where these don't suit your needs?

I guess the real question is: in what context/language are you expecting
to be able to manipulate these variables?
        regards, tom lane

Re: Junk queries with variables?

From

KÖPFERL Robert

Date:

25 February 2005, 22:40:03

PL/pgSQL is not part of template1 = if you create a new database.
Thus you can use for functions just pure SQL or C. To get pgSQL I did that:

CREATE OR REPLACE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
'$libdir/plpgsql', 'plpgsql_call_handler' LANGUAGE 'c' VOLATILE;

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler;


C:\> -----Original Message-----
C:\> From: Steve - DND [mailto:postgres@digitalnothing.com]
C:\> Sent: Donnerstag, 24. Februar 2005 18:41
C:\> To: KÖPFERL Robert; pgsql-sql@postgresql.org
C:\> Subject: RE: Junk queries with variables?
C:\>
C:\>
C:\> >
C:\> > In pgadmins SQL-window SQL is the 'language' of choice. Or it is
C:\> > rather the
C:\> > only language. Thus if you intend to program plTk or
C:\> PL/pgSQL, there's no
C:\> > way around defining a function.
C:\> >
C:\> > (At first you have to define a new language in your schema)
C:\>
C:\> I'm a little confused, is there no way around this, or are
C:\> you saying I need
C:\> to use CREATE LANGUAGE to define a new language to use?
C:\> Currently the only
C:\> language I have for the DB is plpgsql.
C:\>
C:\> Thanks,
C:\> Steve
C:\>
C:\>

Re: Junk queries with variables?

From

"Steve Valaitis"

Date:

28 February 2005, 09:53:43

>
> In pgadmins SQL-window SQL is the 'language' of choice. Or it is
> rather the
> only language. Thus if you intend to program plTk or PL/pgSQL, there's no
> way around defining a function.
>
> (At first you have to define a new language in your schema)

I'm a little confused, is there no way around this, or are you saying I need
to use CREATE LANGUAGE to define a new language to use? Currently the only
language I have for the DB is plpgsql.

Thanks,
Steve

Re: Junk queries with variables?

From

"Sean Davis"

Date:

28 February 2005, 20:41:32

----- Original Message ----- 
From: "Steve Valaitis" <steve@digitalnothing.com>
To: "KÖPFERL Robert" <robert.koepferl@sonorys.at>; 
<pgsql-sql@postgresql.org>
Sent: Thursday, February 24, 2005 12:15 PM
Subject: Re: [SQL] Junk queries with variables?


> >
>> In pgadmins SQL-window SQL is the 'language' of choice. Or it is
>> rather the
>> only language. Thus if you intend to program plTk or PL/pgSQL, there's no
>> way around defining a function.
>>
>> (At first you have to define a new language in your schema)
>
> I'm a little confused, is there no way around this, or are you saying I 
> need
> to use CREATE LANGUAGE to define a new language to use? Currently the only
> language I have for the DB is plpgsql.

Yes.  You need to install the language into the database using CREATE 
LANGUAGE and then you can use the language to create functions.

Sean