Thread: How to write such a query?

How to write such a query?

From

Igor Korot

Date:

06 January 2022, 09:39:59

Hi, ALL,
In SQLite you can write:

SELECT a, b, c FROM foo WHERE id = :id;

where ":id" is the named parameter.

The query above is similar to

SELECT a,b,c FROM foo WHERE id = ?;

except that the parameter has a name.

Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?

Thank you.

Re: How to write such a query?

From

Amul Sul

Date:

06 January 2022, 09:46:16

See prepare statement : https://www.postgresql.org/docs/current/sql-prepare.html

On Thu, Jan 6, 2022 at 12:10 PM Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi, ALL,
> In SQLite you can write:
>
> SELECT a, b, c FROM foo WHERE id = :id;
>
> where ":id" is the named parameter.
>
> The query above is similar to
>
> SELECT a,b,c FROM foo WHERE id = ?;
>
> except that the parameter has a name.
>
> Is there a way to write such a SELECT statement with the
> named parameter in PostgreSQL?
>
> Thank you.
>
>

Re: How to write such a query?

From

"David G. Johnston"

Date:

06 January 2022, 10:00:53

On Wednesday, January 5, 2022, Igor Korot <ikorot01@gmail.com> wrote:

Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?

The server, and its prepared SQL capability, doesn’t understand named parameters. Only numbered ones.

That said, there are a number of different ways to write and execute SQL available to you and each of those provides different extended capabilities. For instance, psql and its variables feature.

David J.

Re: How to write such a query?

From

Ron

Date:

06 January 2022, 10:00:53

On 1/6/22 12:39 AM, Igor Korot wrote:
> Hi, ALL,
> In SQLite you can write:
>
> SELECT a, b, c FROM foo WHERE id = :id;
>
> where ":id" is the named parameter.
>
> The query above is similar to
>
> SELECT a,b,c FROM foo WHERE id = ?;
>
> except that the parameter has a name.
>
> Is there a way to write such a SELECT statement with the
> named parameter in PostgreSQL?
Absolutely.  Of course, the exact method depends on the client.  Are you 
referring to psql?

If so, then here's an example:
$ psql12 -v S=456789012 test
psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
Type "help" for help.

test=# select * from employee where ssn = :'S';
     ssn    |      name       |  ssn_int
-----------+-----------------+-----------
  456789012 | Fred Flintstone | 456789012
(1 row)

In this case, column ssn is of type varchar(9).

-- 
Angular momentum makes the world go 'round.

Re: How to write such a query?

From

Igor Korot

Date:

06 January 2022, 10:06:31

Hi, Ron,

On Thu, Jan 6, 2022 at 1:01 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 1/6/22 12:39 AM, Igor Korot wrote:
> > Hi, ALL,
> > In SQLite you can write:
> >
> > SELECT a, b, c FROM foo WHERE id = :id;
> >
> > where ":id" is the named parameter.
> >
> > The query above is similar to
> >
> > SELECT a,b,c FROM foo WHERE id = ?;
> >
> > except that the parameter has a name.
> >
> > Is there a way to write such a SELECT statement with the
> > named parameter in PostgreSQL?
> Absolutely.  Of course, the exact method depends on the client.  Are you
> referring to psql?
>
> If so, then here's an example:
> $ psql12 -v S=456789012 test
> psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
> Type "help" for help.
>
> test=# select * from employee where ssn = :'S';
>      ssn    |      name       |  ssn_int
> -----------+-----------------+-----------
>   456789012 | Fred Flintstone | 456789012
> (1 row)
>
> In this case, column ssn is of type varchar(9).

Is the syntax available since 9.0+? Or later?
I'm writing C++.

Thank you.

>
> --
> Angular momentum makes the world go 'round.
>
>

Re: How to write such a query?

From

Igor Korot

Date:

06 January 2022, 10:07:56

Hi, David,

On Thu, Jan 6, 2022 at 1:00 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Wednesday, January 5, 2022, Igor Korot <ikorot01@gmail.com> wrote:
>>
>>
>> Is there a way to write such a SELECT statement with the
>> named parameter in PostgreSQL?
>
>
> The server, and its prepared SQL capability, doesn’t understand named parameters.  Only numbered ones.
>
> That said, there are a number of different ways to write and execute SQL available to you and each of those provides
differentextended capabilities.  For instance, psql and its variables feature. 

It looks like your answer contradicts with Ron...

Thank you.

>
> David J.
>

Re: How to write such a query?

From

Ron

Date:

06 January 2022, 10:11:26

On 1/6/22 1:07 AM, Igor Korot wrote:
> Hi, David,
>
> On Thu, Jan 6, 2022 at 1:00 AM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> On Wednesday, January 5, 2022, Igor Korot <ikorot01@gmail.com> wrote:
>>>
>>> Is there a way to write such a SELECT statement with the
>>> named parameter in PostgreSQL?
>>
>> The server, and its prepared SQL capability, doesn’t understand named parameters.  Only numbered ones.
>>
>> That said, there are a number of different ways to write and execute SQL available to you and each of those provides
differentextended capabilities.  For instance, psql and its variables feature.
 
> It looks like your answer contradicts with Ron...

TIMTOWTDI

-- 
Angular momentum makes the world go 'round.

Re: How to write such a query?

From

Igor Korot

Date:

06 January 2022, 10:14:31

Hi, Armul,

On Thu, Jan 6, 2022 at 12:46 AM Amul Sul <sulamul@gmail.com> wrote:
>
> See prepare statement : https://www.postgresql.org/docs/current/sql-prepare.html

The documentation is talking about a way to do it like:

SELECT a, b, c FROM foo WHERE id = $1,

which is equivalent to the

SELECT a, b, c FROM foo WHERE id = ?;

i.e. using unnamed parameter.

Thank you.

>
> On Thu, Jan 6, 2022 at 12:10 PM Igor Korot <ikorot01@gmail.com> wrote:
> >
> > Hi, ALL,
> > In SQLite you can write:
> >
> > SELECT a, b, c FROM foo WHERE id = :id;
> >
> > where ":id" is the named parameter.
> >
> > The query above is similar to
> >
> > SELECT a,b,c FROM foo WHERE id = ?;
> >
> > except that the parameter has a name.
> >
> > Is there a way to write such a SELECT statement with the
> > named parameter in PostgreSQL?
> >
> > Thank you.
> >
> >

Re: How to write such a query?

From

Ron

Date:

06 January 2022, 10:14:36

On 1/6/22 1:06 AM, Igor Korot wrote:

Hi, Ron,

On Thu, Jan 6, 2022 at 1:01 AM Ron <ronljohnsonjr@gmail.com> wrote:

On 1/6/22 12:39 AM, Igor Korot wrote:

Hi, ALL,
In SQLite you can write:

SELECT a, b, c FROM foo WHERE id = :id;

where ":id" is the named parameter.

The query above is similar to

SELECT a,b,c FROM foo WHERE id = ?;

except that the parameter has a name.

Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?

Absolutely.  Of course, the exact method depends on the client.  Are you
referring to psql?

If so, then here's an example:
$ psql12 -v S=456789012 test
psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
Type "help" for help.

test=# select * from employee where ssn = :'S';     ssn    |      name       |  ssn_int
-----------+-----------------+-----------  456789012 | Fred Flintstone | 456789012
(1 row)

In this case, column ssn is of type varchar(9).

Is the syntax available since 9.0+? Or later?
I'm writing C++.

Like I said before... "the exact method depends on the client". The C++ client library is not the same as the psql application.

IOW, read the C+ client library documentation.

--
Angular momentum makes the world go 'round.

Re: How to write such a query?

From

Dmitry Igrishin

Date:

06 January 2022, 11:17:07

On Thu, Jan 6, 2022, 09:40 Igor Korot <ikorot01@gmail.com> wrote:

Hi, ALL,
In SQLite you can write:

SELECT a, b, c FROM foo WHERE id = :id;

where ":id" is the named parameter.

The query above is similar to

SELECT a,b,c FROM foo WHERE id = ?;

except that the parameter has a name.

Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?

Named parameters of prepared statements are implemented in my C++ library Pgfe.