Thread: Using DEFAULT as a parameter value with PQexecPrepare()
I am currently dynamically building a SQL command in my C code like that executes SQL statement like:
INSERT INTO t (c1,c2,c3,c4,c5) VALUES (‘abc’, 1, DEFAULT, 9, 3);
I execute the command using PQexec() of libpq.
The application logic determines when to use the default values for several columns.
The first two columns do not default values so they must always be specified.
I was looking for a to use a prepared statement for this operation.
PREPARE myinsert AS “INSERT INTO t ( c1, c2, c3, c4, c5) VALUES ( $1, $2, $3, $4, $5);
Now I want to execute this prepared statement something like:
EXECUTE myinsert ( ‘abc’, 1, DEFAULT, 9, 3);
Is there any way to specify the column’s default value as a parameter value?
Doesn’t look like I can do this using the PQexecPrepared() function of libpq.
Doesn’t look like I can do this using PQexecParams() as well.
I am using version 8.4 of PostgreSQL.
Thanks,
Pete Rothermel
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > I was looking for a to use a prepared statement for this operation. > > PREPARE myinsert AS "INSERT INTO t ( c1, c2, c3, c4, c5) VALUES ( $1, $2, $3, $4, $5); > > Now I want to execute this prepared statement something like: > > EXECUTE myinsert ( 'abc', 1, DEFAULT, 9, 3); > > Is there any way to specify the column's default value as a parameter value? You will need to have separate prepared statements. In the case above, you can use either: PREPARE myinsert2 AS INSERT INTO t(c1,c2,c3,c4,c5) VALUES ($1,$2,DEFAULT,$3,$4); or PREPARE myinsert2 AS INSERT INTO t(c1,c2,c4,c5) VALUES ($1,$2,$3,$4); - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201204052214 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAk9+UiQACgkQvJuQZxSWSsikAwCg/f28B4vLzPvurQtf8hmdhqO4 dHgAoIR8nuy89zN3t46FdoQMDm3oWIE3 =wCLM -----END PGP SIGNATURE-----
I simplified my example somewhat. I usually have six of these "optional" parameters.
The number of prepared statements would be too many for this approach.
I will follow your advice in the cases where I just one or two of the "optional" parameters.
Looks like I will need to dynamically build my SQL command as:
INSERT INTO t ( c1, c2, c5, c8, c11 ) VALUES ( $1, $2, $3, $4, $5 );
when the number of "optional" parameters is larger.
I can at least use PQexecParams() to get some SQL injection protection and avoid the escaping and quoting of the
parametervalues.
Thanks for the advice
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Greg Sabino Mullane
Sent: Thursday, April 05, 2012 7:18 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using DEFAULT as a parameter value with PQexecPrepare()
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> I was looking for a to use a prepared statement for this operation.
>
> PREPARE myinsert AS "INSERT INTO t ( c1, c2, c3, c4, c5) VALUES ( $1, $2, $3, $4, $5);
>
> Now I want to execute this prepared statement something like:
>
> EXECUTE myinsert ( 'abc', 1, DEFAULT, 9, 3);
>
> Is there any way to specify the column's default value as a parameter value?
You will need to have separate prepared statements. In the case above,
you can use either:
PREPARE myinsert2 AS INSERT INTO t(c1,c2,c3,c4,c5) VALUES ($1,$2,DEFAULT,$3,$4);
or
PREPARE myinsert2 AS INSERT INTO t(c1,c2,c4,c5) VALUES ($1,$2,$3,$4);
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201204052214
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAk9+UiQACgkQvJuQZxSWSsikAwCg/f28B4vLzPvurQtf8hmdhqO4
dHgAoIR8nuy89zN3t46FdoQMDm3oWIE3
=wCLM
-----END PGP SIGNATURE-----
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > I simplified my example somewhat. I usually have six of > these "optional" parameters. The number of prepared statements > would be too many for this approach. > I will follow your advice in the cases where I just one or two > of the "optional" parameters. Well, it shouldn't be too bad if you can build them dynamically and let the app track them, e.g. a hash/LL with the column names smushed together. A little more work, but worth it if you are calling these often. ... > I can at least use PQexecParams() to get some SQL injection > protection and avoid the escaping and quoting of the parameter values. One other way I should mention is that if your app knows it, it can always pass in the default value(s) directly. :) - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201204061612 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAk9/TpQACgkQvJuQZxSWSsjRdwCdEjDz0K54rNlwb+nECXoT1TMB VvIAn325b3Sjcag0MqaiPtsPpm+Q1/zj =aZDP -----END PGP SIGNATURE-----