Postgres Pro
Downloads
Home   >   mailing lists

Thread: Binding Parameters

Binding Parameters

From
Postgres User
Date:
I'm new to PostgreSQL, and am wondering for BindSQL is intergrated in
Postgres or in the future for version 8.

Example:

SELECT * FROM sample_table WHERE computer = :1;

Any pointers will help.
Thanks,
J

Re: Binding Parameters

From
Andrew Hammond
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Postgres User wrote:
| I'm new to PostgreSQL, and am wondering for BindSQL is intergrated in
| Postgres or in the future for version 8.
|
| Example:
|
| SELECT * FROM sample_table WHERE computer = :1;
|
| Any pointers will help.

Do you mean PREPARE / EXECUTE?

/* ahammond@[local]:5432/ahammond =# */ \d foo
~                       Table "public.foo"
~ Column |  Type   |                  Modifiers
- --------+---------+----------------------------------------------
~ foo_id | integer | not null default nextval('foo_id_seq'::text)
~ name   | text    | not null
Indexes:
~    "foo_id_idx" unique, btree (foo_id)
~    "foo_name_idx" unique, btree (name)

/* ahammond@[local]:5432/ahammond =# */ SELECT * FROM foo;
~ foo_id | name
- --------+------
~      1 | a
~      2 | b
~      3 | c
~      4 | d
~      5 | f
(5 rows)

/* ahammond@[local]:5432/ahammond =# */ PREPARE foo_name (integer) AS
SELECT name FROM foo WHERE foo_id = $1;
PREPARE
/* ahammond@[local]:5432/ahammond =# */ EXECUTE foo_name (1);
~ name
- ------
~ a
(1 row)

/* ahammond@[local]:5432/ahammond =# */ EXECUTE foo_name (4);
~ name
- ------
~ d
(1 row)

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFBQ0CJgfzn5SevSpoRAqyPAKDPM6BGRBT24nUJvaDePgcsBKVHhgCgyH0d
/qANwzyTD/HyNbBYCxTOFic=
=B1y3
-----END PGP SIGNATURE-----

Re: Binding Parameters

From
Postgres User
Date:
>
> Do you mean PREPARE / EXECUTE?
>
> /* ahammond@[local]:5432/ahammond =# */ \d foo
> ~                       Table "public.foo"
> ~ Column |  Type   |                  Modifiers
> - --------+---------+----------------------------------------------
> ~ foo_id | integer | not null default nextval('foo_id_seq'::text)
> ~ name   | text    | not null
> Indexes:
> ~    "foo_id_idx" unique, btree (foo_id)
> ~    "foo_name_idx" unique, btree (name)
>
> /* ahammond@[local]:5432/ahammond =# */ SELECT * FROM foo;
> ~ foo_id | name
> - --------+------
> ~      1 | a
> ~      2 | b
> ~      3 | c
> ~      4 | d
> ~      5 | f
> (5 rows)
>
> /* ahammond@[local]:5432/ahammond =# */ PREPARE foo_name (integer) AS
> SELECT name FROM foo WHERE foo_id = $1;
> PREPARE
> /* ahammond@[local]:5432/ahammond =# */ EXECUTE foo_name (1);
> ~ name
> - ------
> ~ a
> (1 row)
>
> /* ahammond@[local]:5432/ahammond =# */ EXECUTE foo_name (4);
> ~ name
> - ------
> ~ d
> (1 row)
>
Thanks for the short tutorial... I think that I've gotten the point.  I
was searching online and nothing proved useful.

Anymore more examples or direction on finding any of bind parameter is
appreciated.
J

PREPARE/EXECUTE questions

From
Postgres User
Date:
I'm writing a web application inwhich clients will login/out among other
stuff. I have be reading on prepared statements and leave you all with
an open-ended question.  Are prepared statements better and how can I
program them to not end at the once the session is over?

Thanks, any other suggestions are welcome.
J

Re: PREPARE/EXECUTE questions

From
"Joshua D. Drake"
Date:
Postgres User wrote:

> I'm writing a web application inwhich clients will login/out among
> other stuff. I have be reading on prepared statements and leave you
> all with an open-ended question.  Are prepared statements better and
> how can I program them to not end at the once the session is over?

Use a connection pooler like pgpool.

Sincerely,

Joshua D. Drake



>
> Thanks, any other suggestions are welcome.
> J
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: PREPARE/EXECUTE questions... custom functions?

From
Postgres User
Date:
Joshua D. Drake wrote:

> Use a connection pooler like pgpool.

Thanks for the suggestion, I will look into it and test. There seems to
some overhead using that tool. What about custom functions, written in
SQL or C?  They seem to work until the database is shut down, any
thoughts about that for a custom application?  Pros/Cons...

Thanks in advance,
J

SQL Functions

From
Postgres User
Date:
What are the advantages to SQL Functions?  I'm looking to optimize
'default' PostgreSQL environments for clients. I'm writing an
application and looking to put functions to optimize just that table,
etc. They don't want to have a full-time database admin (surprise!).

Thanks,
J