How to call Postgres function from .NET? [closed]

Question

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed 4 hours ago.

Improve this question

I tried this code:

CREATE OR REPLACE FUNCTION public.t4(in iid int, OUT n1 text, OUT n2 text)  
LANGUAGE plpgsql
AS $function$
    BEGIN 
            SELECT DISTINCT n1=l.name, n2=l.comment from public.languages l where l.id=iid;
    END;        
$function$;

 NpgsqlConnection connection = new NpgsqlConnection(ConString);
 connection.Open();
 NpgsqlCommand cmd = new NpgsqlCommand("SELECT * from public.t4()", connection);
 cmd.Parameters.Add(new NpgsqlParameter("iid", DbType.Int32) { Direction = ParameterDirection.Input });
 cmd.Parameters[0].Value = 18;
 cmd.Parameters.Add(new NpgsqlParameter("n1", DbType.String) { Direction = ParameterDirection.Output });
 cmd.Parameters.Add(new NpgsqlParameter("n2", DbType.String) { Direction = ParameterDirection.Output });
 cmd.ExecuteNonQuery();
 t1.Text = cmd.Parameters[1].Value.ToString();
 t2.Text = cmd.Parameters[2].Value.ToString();

And I got an error:

Npgsql.PostgresException: '42883: function public.t4() does not exist.

What am I doing wrong?

The message is correct function public.t4() does not exist, but public.t4(in iid int, OUT n1 text, OUT n2 text) does. I don't any placeholders in the query for the parameters you are trying to add. See here stackoverflow.com/questions/54691579/… fro examples. — Adrian Klaver
– Adrian Klaver, Commented yesterday

Guru Stron · Accepted Answer · 2025-11-29 07:53:48Z

3

You need to pass parameters to your function invocation - see the Npgsql docs:

NpgsqlCommand cmd = new NpgsqlCommand("SELECT * from public.t4(@iid)", connection);

Otherwise your code tries to call a t4 function without input parameters and there is no t4 function accepting no input parameters.

Also you need to do is to fix the function itself:

CREATE OR REPLACE FUNCTION public.t4(in iid int, OUT n1 text, OUT n2 text)
    LANGUAGE plpgsql
AS $function$
BEGIN
    SELECT DISTINCT l.name, l.comment
    from public.languages l
    where l.id=iid
    into n1, n2; -- <--- HERE!
END;
$function$;

answered yesterday

Guru Stron

151k11 gold badges186 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2025-11-29 20:27:52Z

0

You need to add all three parameters to the function call in SQL

using NpgsqlConnection connection = new NpgsqlConnection(ConString);
const string query = """
    SELECT * from public.t4(iid, n1, n2);
    """;
using NpgsqlCommand cmd = new NpgsqlCommand(query, connection);
cmd.Parameters.Add("iid", DbType.Int32).Value = 18;
cmd.Parameters.Add("n1", DbType.String).Direction = ParameterDirection.Output;
cmd.Parameters.Add("n2", DbType.String).Direction = ParameterDirection.Output;
conection.Open();
cmd.ExecuteNonQuery();

Note also the use of a raw query string which makes it easier to format, and using to dispose. Also consider using await and async functions.

answered 18 hours ago

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

1 Comment

Guru Stron 13 hours ago

"You need to add all three parameters to the function call in SQL" - only the in parameter is needed based on my tests (latest PG and Npgsql though)

Collectives™ on Stack Overflow

How to call Postgres function from .NET? [closed]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related