Binding Parameters

[Postgres User]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Andrew Hammond]

-----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 ***@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-----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Postgres User]

Post by Andrew Hammond
Do you mean PREPARE / EXECUTE?
~ Table "public.foo"
~ Column | Type | Modifiers
- --------+---------+----------------------------------------------
~ foo_id | integer | not null default nextval('foo_id_seq'::text)
~ name | text | not null
~ "foo_id_idx" unique, btree (foo_id)
~ "foo_name_idx" unique, btree (name)
~ foo_id | name
- --------+------
~ 1 | a
~ 2 | b
~ 3 | c
~ 4 | d
~ 5 | f
(5 rows)
SELECT name FROM foo WHERE foo_id = $1;
PREPARE
~ name
- ------
~ a
(1 row)
~ 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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Postgres User]

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Joshua D. Drake]

Post by Postgres User
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

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

[Postgres User]

Post by Joshua D. Drake
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Postgres User]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Raymond Auge]

Hello Postgres User,

I'd have to say that functions have the same "function" in SQL as they do in any language; a logical grouping of often repeated opperations.

So, if you have a repeated sequence like

query1(args);
if(result of query1 does this){
query2(args);
}
else{
query3(args);
}

then consider that you have 3 transactions with the database.

But, if all that was put into a SQL function, you have 1 transaction; "functions in SQL are atomic".

As well, in you code you have

sql_function(args);

and that's it...

clean and very fast.

--------------------------------------------------------------
Raymond Augé, B.COSC
Spécialiste en médiatisation de la formation/Multimedia specialist
Université Laurentienne / Laurentian University
<***@laurentienne.ca> / <***@laurentian.ca>
[phone] x3934
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly