Filling Winforms datagrid using Postgres stored procedure

Question

I am trying to get data from Postgres table with a help of stored procedure and fill a Winforms DataGrid with it.

This is the stored procedure:

CREATE OR REPLACE PROCEDURE public.get_list()
 LANGUAGE sql
BEGIN ATOMIC
 SELECT field1,
        field2,
        field3
    FROM some_table;
END;

Here's my C# code:

DataTable dtt = new DataTable();
string ConString = "Server=x.xxx.xx.xxx;Port=5432;User Id=xxx;Password=xxx;Database=xxx;";

NpgsqlConnection connection = new NpgsqlConnection(ConString);
connection.Open();
NpgsqlCommand cmd = new NpgsqlCommand("CALL public.get_list()");            

try
{
    cmd.Connection = connection;
    cmd.CommandType = System.Data.CommandType.StoredProcedure;

    using (var dataReader = cmd.ExecuteReader())
    {
        if (dataReader.HasRows)
        {
            GridView.Visible = true;                        
            dtt.Load(dataReader);                        

            GridView.DataSource = dtt;
            GridView.Update();    
        }
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

After executing this code, the DataReader returns 0 rows. The stored procedure returns nothing: fiddle

CALL public.get_list();

CALL

What am I doing wrong?

Maybe the analog of a stored procedure in other RDBMSs (for example in SQL Server) in Postgres is a function?

The GridView may not be getting refreshed. Sometimes setting DataSource to null and then to the datatable will refresh the view : GridView.DataSource = null; GridView.DataSource = dtt; — jdweng
– jdweng, Commented Sep 5 at 16:58
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community
– Community Bot, Commented Sep 5 at 17:32
a store procedure is not meant to return anything... use a function instead (and access it via select * from public.get_list()) — JGH
– JGH, Commented Sep 5 at 18:31
Check How to return result of a SELECT inside a function in PostgreSQL? — Diego Ferruchelli
– Diego Ferruchelli, Commented Sep 5 at 20:16
Downvoted because trying to return data from a stored procedure makes no sense and the extensive documentation makes this clear — Richard Huxton
– Richard Huxton, Commented Sep 5 at 20:36

Zegarek · Accepted Answer · 2025-09-08 09:04:05Z

Quoting the CALL doc:

If the procedure has any output parameters, then a result row will be returned, containing the values of those parameters.

You just need out or inout params to return things from it.
_{demo at db<>fiddle}

CREATE OR REPLACE PROCEDURE public.get_list(
  param1 inout some_table.field1%type default null,
  param2 inout some_table.field2%type default null,
  param3 inout some_table.field3%type default null)
 LANGUAGE sql
BEGIN ATOMIC
 SELECT field1,
        field2,
        field3
    FROM some_table
 ORDER BY 1,2,3
 LIMIT 1;
END;

Making them inout..default null lets you skip them in your invocation:

CALL public.get_list();

param1	param2	param3
1	1	1

It's worth pointing out support for unquoted SQL routine bodies was added in version 14, fairly recently. 36.5. Query Language (SQL) Functions describes how they return:

SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list. In the simple (non-set) case, the first row of the last query's result will be returned. (Bear in mind that “the first row” of a multirow result is not well-defined unless you use ORDER BY.)

Procedures can only return at most one row this way. As already suggested, you can also switch to a function, that returns table or returns setof:

DROP ROUTINE IF EXISTS public.get_list();
DROP ROUTINE IF EXISTS public.get_list(
  some_table.field1%type,
  some_table.field2%type,
  some_table.field3%type);

CREATE OR REPLACE FUNCTION public.get_list()
 RETURNS TABLE (
  field1 some_table.field1%type,
  field2 some_table.field2%type,
  field3 some_table.field3%type)
 LANGUAGE sql
BEGIN ATOMIC
 SELECT s.field1,
        s.field2,
        s.field3
 FROM some_table AS s;
END;

Then select it instead of calling:

SELECT * FROM public.get_list();

Collectives™ on Stack Overflow

Filling Winforms datagrid using Postgres stored procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related