Обсуждение: How to write such a query?

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.

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.
>
>

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

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.

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.
>
>

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.
>

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.

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.
> >
> >

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++.