Обсуждение: libpq simple SELECT

Hi, ALL,
On the page https://www.postgresql.org/docs/current/libpq-example.html
in the first program there is a following comment:

[code]

/*
     * Our test case here involves using a cursor, for which we must be inside
     * a transaction block.  We could do the whole thing with a single
     * PQexec() of "select * from pg_database", but that's too trivial to make
     * a good example.
     */
[/code]

I just tried the following code:

[code]
    std::wstring query1 = L"SELECT t.table_catalog AS catalog,
t.table_schema AS schema, t.table_name AS table, u.usename AS owner,
c.oid AS table_id FROM information_schema.tables t,
pg_catalog.pg_class c, pg_catalog.pg_user u WHERE t.table_name =
c.relname AND c.relowner = usesysid AND (t.table_type = 'BASE TABLE'
OR t.table_type = 'VIEW' OR t.table_type = 'LOCAL TEMPORARY') ORDER BY
table_name;";
        res = PQexec( m_db, m_pimpl->m_myconv.to_bytes( query1.c_str()
).c_str() );
         for( int i = 0; i < PQntuples( res ); i++ )
         {
some code handling the results
         }
[/code]

The loop is executed exactly 1 time.

My question is - according to the comment above I don't have to use cursor,
but it looks like it's unavoidable?

Thank you.

On Tue, 2025-12-16 at 21:49 -0800, Igor Korot wrote:
> I just tried the following code:
>
> [code]
>     std::wstring query1 = L"SELECT t.table_catalog AS catalog,
> t.table_schema AS schema, t.table_name AS table, u.usename AS owner,
> c.oid AS table_id FROM information_schema.tables t,
> pg_catalog.pg_class c, pg_catalog.pg_user u WHERE t.table_name =
> c.relname AND c.relowner = usesysid AND (t.table_type = 'BASE TABLE'
> OR t.table_type = 'VIEW' OR t.table_type = 'LOCAL TEMPORARY') ORDER BY
> table_name;";
>         res = PQexec( m_db, m_pimpl->m_myconv.to_bytes( query1.c_str()
> ).c_str() );
>          for( int i = 0; i < PQntuples( res ); i++ )
>          {
> some code handling the results
>          }
> [/code]
>
> The loop is executed exactly 1 time.
>
> My question is - according to the comment above I don't have to use cursor,
> but it looks like it's unavoidable?

I see no cursor in your example...

Yours,
Laurenz Albe

On Wed, 2025-12-17 at 09:42 -0800, Igor Korot wrote:
> On Tue, Dec 16, 2025 at 11:34 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Tue, 2025-12-16 at 21:49 -0800, Igor Korot wrote:
> > > I just tried the following code:
> > >
> > > [code]
> > >      std::wstring query1 = L"SELECT t.table_catalog AS catalog,
> > > t.table_schema AS schema, t.table_name AS table, u.usename AS owner,
> > > c.oid AS table_id FROM information_schema.tables t,
> > > pg_catalog.pg_class c, pg_catalog.pg_user u WHERE t.table_name =
> > > c.relname AND c.relowner = usesysid AND (t.table_type = 'BASE TABLE'
> > > OR t.table_type = 'VIEW' OR t.table_type = 'LOCAL TEMPORARY') ORDER BY
> > > table_name;";
> > >          res = PQexec( m_db, m_pimpl->m_myconv.to_bytes( query1.c_str()
> > > ).c_str() );
> > >           for( int i = 0; i < PQntuples( res ); i++ )
> > >           {
> > > some code handling the results
> > >           }
> > > [/code]
> > >
> > > The loop is executed exactly 1 time.
> > >
> > > My question is - according to the comment above I don't have to use cursor,
> > > but it looks like it's unavoidable?
> >
> > I see no cursor in your example...
>
> That is my question.
>
> The code shows cursor but the comment indicate that the cursor is not required.

I am still confused.

You quoted a comment from a sample program in the documentation, and that
example is actually using a cursor:

    res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");

But your code does not use a cursor.  So why should the comment apply to it?

Yours,
Laurenz Albe

Laurenz,

On Wed, Dec 17, 2025 at 12:22 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Wed, 2025-12-17 at 09:42 -0800, Igor Korot wrote:
> > On Tue, Dec 16, 2025 at 11:34 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > > On Tue, 2025-12-16 at 21:49 -0800, Igor Korot wrote:
> > > > I just tried the following code:
> > > >
> > > > [code]
> > > >      std::wstring query1 = L"SELECT t.table_catalog AS catalog,
> > > > t.table_schema AS schema, t.table_name AS table, u.usename AS owner,
> > > > c.oid AS table_id FROM information_schema.tables t,
> > > > pg_catalog.pg_class c, pg_catalog.pg_user u WHERE t.table_name =
> > > > c.relname AND c.relowner = usesysid AND (t.table_type = 'BASE TABLE'
> > > > OR t.table_type = 'VIEW' OR t.table_type = 'LOCAL TEMPORARY') ORDER BY
> > > > table_name;";
> > > >          res = PQexec( m_db, m_pimpl->m_myconv.to_bytes( query1.c_str()
> > > > ).c_str() );
> > > >           for( int i = 0; i < PQntuples( res ); i++ )
> > > >           {
> > > > some code handling the results
> > > >           }
> > > > [/code]
> > > >
> > > > The loop is executed exactly 1 time.
> > > >
> > > > My question is - according to the comment above I don't have to use cursor,
> > > > but it looks like it's unavoidable?
> > >
> > > I see no cursor in your example...
> >
> > That is my question.
> >
> > The code shows cursor but the comment indicate that the cursor is not required.
>
> I am still confused.
>
> You quoted a comment from a sample program in the documentation, and that
> example is actually using a cursor:
>
>     res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
>
> But your code does not use a cursor.  So why should the comment apply to it?

This is a comment:

/*
     * Our test case here involves using a cursor, for which we must be inside
     * a transaction block.  We could do the whole thing with a single
     * PQexec() of "select * from pg_database", but that's too trivial to make
     * a good example.
     */

When I read it my understanding is:

It is possible to write the code without a cursor..
However it will be trivial and so the code will use CURSOR.
But the usage of CURSOR is actually not required - it is used
in the docs only to make a good example.

However, when I tried to write the code without the CURSOR
it seems that CURSOR usage is a must and not optional, which
contradicts the comment above.

I hope now my question is clear. I want to know if using CURSR
is a must.

Thank you.


>
> Yours,
> Laurenz Albe

I hope now my question is clear. I want to know if using CURSR
is a must.

David,,

On Wed, Dec 17, 2025 at 1:14 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Wednesday, December 17, 2025, Igor Korot <ikorot01@gmail.com> wrote:
>>
>>
>> I hope now my question is clear. I want to know if using CURSR
>> is a must.
>
>
> No it is not.  But there isn’t an example of doing so without a cursor because it is deemed trivial enough that a
competentC writer can figure it out.  In particular, it basically is just the same code as the “fetch” portion of the
example,but the select command is supplied instead of a fetch command.  Both of those SQL commands result is tuples
beinggenerated. 

So what do I do after calling PQexec()? And why is my code running only once?

Thank you.

>
> David J.
>

On Wed, 2025-12-17 at 13:07 -0800, Igor Korot wrote:
> This is a comment:
>
> /*
>      * Our test case here involves using a cursor, for which we must be inside
>      * a transaction block.  We could do the whole thing with a single
>      * PQexec() of "select * from pg_database", but that's too trivial to make
>      * a good example.
>      */
>
> When I read it my understanding is:
>
> It is possible to write the code without a cursor..
> However it will be trivial and so the code will use CURSOR.
> But the usage of CURSOR is actually not required - it is used
> in the docs only to make a good example.
>
> However, when I tried to write the code without the CURSOR
> it seems that CURSOR usage is a must and not optional, which
> contradicts the comment above.
>
> I hope now my question is clear. I want to know if using CURSR
> is a must.

Sorry that it took me so long to understand what you mean.

Your PQexec call should return all rows.  If you don't get all rows, there
must be some other problem with your code.  You didn't show enough of the
code for me to see where the problem is.

Yours,
Laurenz Albe