pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJunk queries with variables?
13+ messages / 8 participants
[nested] [flat]
* Junk queries with variables?
@ 2005-02-24 06:12 Steve - DND <postgres@digitalnothing.com>
2005-02-24 07:40 ` Re: Junk queries with variables? Michael Fuhr <mike@fuhr.org>
2005-02-24 08:23 ` Re: Junk queries with variables? Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 13+ messages in thread
From: Steve - DND @ 2005-02-24 06:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
2005-02-24 06:12 Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
@ 2005-02-24 07:40 ` Michael Fuhr <mike@fuhr.org>
2005-02-24 17:10 ` Re: Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
2005-02-24 17:15 ` Re: Junk queries with variables? Michael Fuhr <mike@fuhr.org>
1 sibling, 2 replies; 13+ messages in thread
From: Michael Fuhr @ 2005-02-24 07:40 UTC (permalink / raw)
To: Steve - DND <postgres@digitalnothing.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
2005-02-24 06:12 Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
2005-02-24 07:40 ` Re: Junk queries with variables? Michael Fuhr <mike@fuhr.org>
@ 2005-02-24 17:10 ` Steve - DND <postgres@digitalnothing.com>
2005-02-24 17:17 ` Re: Junk queries with variables? Bricklen Anderson <BAnderson@PresiNET.com>
1 sibling, 1 reply; 13+ messages in thread
From: Steve - DND @ 2005-02-24 17:10 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
2005-02-24 06:12 Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
2005-02-24 07:40 ` Re: Junk queries with variables? Michael Fuhr <mike@fuhr.org>
2005-02-24 17:10 ` Re: Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
@ 2005-02-24 17:17 ` Bricklen Anderson <BAnderson@PresiNET.com>
0 siblings, 0 replies; 13+ messages in thread
From: Bricklen Anderson @ 2005-02-24 17:17 UTC (permalink / raw)
To: Steve - DND <postgres@digitalnothing.com>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
2005-02-24 06:12 Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
2005-02-24 07:40 ` Re: Junk queries with variables? Michael Fuhr <mike@fuhr.org>
@ 2005-02-24 17:15 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 13+ messages in thread
From: Michael Fuhr @ 2005-02-24 17:15 UTC (permalink / raw)
To: Steve - DND <postgres@digitalnothing.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
2005-02-24 06:12 Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
@ 2005-02-24 08:23 ` Richard Huxton <dev@archonet.com>
2005-02-24 17:11 ` Re: Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
1 sibling, 1 reply; 13+ messages in thread
From: Richard Huxton @ 2005-02-24 08:23 UTC (permalink / raw)
To: Steve - DND <postgres@digitalnothing.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
2005-02-24 06:12 Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
2005-02-24 08:23 ` Re: Junk queries with variables? Richard Huxton <dev@archonet.com>
@ 2005-02-24 17:11 ` Steve - DND <postgres@digitalnothing.com>
2005-02-24 17:28 ` Re: Junk queries with variables? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Steve - DND @ 2005-02-24 17:11 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
2005-02-24 06:12 Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
2005-02-24 08:23 ` Re: Junk queries with variables? Richard Huxton <dev@archonet.com>
2005-02-24 17:11 ` Re: Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
@ 2005-02-24 17:28 ` Richard Huxton <dev@archonet.com>
2005-02-24 17:50 ` Re: Junk queries with variables? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Richard Huxton @ 2005-02-24 17:28 UTC (permalink / raw)
To: Steve - DND <postgres@digitalnothing.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
2005-02-24 06:12 Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
2005-02-24 08:23 ` Re: Junk queries with variables? Richard Huxton <dev@archonet.com>
2005-02-24 17:11 ` Re: Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
2005-02-24 17:28 ` Re: Junk queries with variables? Richard Huxton <dev@archonet.com>
@ 2005-02-24 17:50 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2005-02-24 17:50 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Steve - DND <postgres@digitalnothing.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
@ 2005-02-24 09:08 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-02-24 17:15 ` Re: Junk queries with variables? Steve Valaitis <steve@digitalnothing.com>
0 siblings, 1 reply; 13+ messages in thread
From: KÖPFERL Robert @ 2005-02-24 09:08 UTC (permalink / raw)
To: pgsql-sql
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:\>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
2005-02-24 09:08 Re: Junk queries with variables? KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-02-24 17:15 ` Steve Valaitis <steve@digitalnothing.com>
2005-02-28 11:24 ` Re: Junk queries with variables? Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 1 reply; 13+ messages in thread
From: Steve Valaitis @ 2005-02-24 17:15 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
2005-02-24 09:08 Re: Junk queries with variables? KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-02-24 17:15 ` Re: Junk queries with variables? Steve Valaitis <steve@digitalnothing.com>
@ 2005-02-28 11:24 ` Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 0 replies; 13+ messages in thread
From: Sean Davis @ 2005-02-28 11:24 UTC (permalink / raw)
To: Steve Valaitis <steve@digitalnothing.com>; KÖPFERL Robert <robert.koepferl@sonorys.at>; pgsql-sql
----- 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Junk queries with variables?
@ 2005-02-25 08:33 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 13+ messages in thread
From: KÖPFERL Robert @ 2005-02-25 08:33 UTC (permalink / raw)
To: pgsql-sql
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:\>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2005-02-28 11:24 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-24 06:12 Junk queries with variables? Steve - DND <postgres@digitalnothing.com>
2005-02-24 07:40 ` Michael Fuhr <mike@fuhr.org>
2005-02-24 17:10 ` Steve - DND <postgres@digitalnothing.com>
2005-02-24 17:17 ` Bricklen Anderson <BAnderson@PresiNET.com>
2005-02-24 17:15 ` Michael Fuhr <mike@fuhr.org>
2005-02-24 08:23 ` Richard Huxton <dev@archonet.com>
2005-02-24 17:11 ` Steve - DND <postgres@digitalnothing.com>
2005-02-24 17:28 ` Richard Huxton <dev@archonet.com>
2005-02-24 17:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-24 09:08 Re: Junk queries with variables? KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-02-24 17:15 ` Steve Valaitis <steve@digitalnothing.com>
2005-02-28 11:24 ` Sean Davis <sdavis2@mail.nih.gov>
2005-02-25 08:33 Re: Junk queries with variables? KÖPFERL Robert <robert.koepferl@sonorys.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox