Calling a PL/PGSQL Function with Nullable Parameters

Question

PROBLEM:

I'm having issues with a function that I've created in Postgres (9.1) with plpgsql language. I'm coming from the world of SQL Server, so there's a bit of a language gap here.

My problem is that postgres doesn't seem to be assigning the parameters I'm passing in correctly.

Here's my function definition:

CREATE OR REPLACE FUNCTION func1 (
    IN param1 character varying,
    IN param2 character varying DEFAULT NULL::character varying,
    IN param3 int DEFAULT NULL::int)
RETURNS void
AS $$
BEGIN
    INSERT INTO table1
    (
        col1
        , col2
        , col3
    )
    VALUES
    (
        $1
        , $2
        , $3
    )
END;
$$ LANGUAGE plpgsql;

The function itself works fine while testing in the database environment, but I'm currently trying to call it from C++ via ODBC connection.

Here's where I set the parameters:

pCmd->paramIn( "param1", (char *)name.getString().c_str() );
pCmd->paramIn( "param3", 100 );

In this case, I have not assigned param2 to any value. It's nullable - shouldn't be a problem.

The error I'm getting is:

ERROR: function func1(unknown, integer) does not exist;

From what I can see, the call is trying to assign the parameters in order, ignoring the parameter names.

QUESTION:

1 - How can I call this function to make this work? Do I have to pass "NULL" in as the missing parameters, and make sure that they're all in order?

2 - If I'm using an ODBC connection, which I most certainly am, should I call a SQL language function which, in turn, calls a PLPGSQL function? It's a bit convoluted, but I'd be open to trying it.

3 - (not a question) Please don't suggest, "Don't use ODBC." Also, please don't try changing the innards of the function, unless necessary. My primary concern right now is learning how to make a function call in postgres via ODBC.

Thank you all in advance for taking the time to read this, and a big thanks to anyone who answers.

UPDATE:

Here's the DboCommand:

DboCommand *cmd;
cmd = new DboCommand(dbConnection, "{call func1 (?) (?)}");

Which used to be this

cmd = new DboCommand(dbConnection, "func1");

Now, the error is:

the # of binded parameters < the # of parameter markers

Daniel Vérité · Accepted Answer · 2012-03-27 18:09:15Z

Question #1: yes, you have to pass NULL and care about the order (and the types).

In theory, PostgreSQL functions can be called with parameters in named or positional notation. It's explained here: http://www.postgresql.org/docs/9.1/static/sql-syntax-calling-funcs.html

When using only the positional notation, it is possible to omit the Nth parameter only if any following parameter is omitted as well. Since your ODBC driver clearly uses this notation, you must specify the 2nd parameter if you want to specify the 3rd. Presumably, the names that you choose in your client code for the parameters are completely unrelated to the names of the plpgsql function declaration, it's the positions and arguments types that matter for matching the call to an existing function.

Question #2: introducing a SQL stub function cannot help with this.

Question #3: The possibility of naming parameters from the point of view of the caller is recent (appeared in PG 9.0 I think) so it might be why the ODBC driver has no support for it.

dbenhur · Accepted Answer · 2012-03-27 17:35:40Z

1

ERROR: function func1(unknown, integer) does not exist;

This message indicates the problem: Postgres can't figure out what function to call because it has an incomplete function signature, there's no function named func1 that takes an unknown type as its first arg and an integer as it's second. Try casting your placeholders to the expected type when you call the function so postgres can resolve the signature, eg, something like: func1(:param1::character varying, :param2::character varying, :param3::integer) or func1( CAST(:param1 as character varying), CAST(:param2 as character varying), CAST(:param3 as integer) ). You didn't post the function call or the sql statement, so I from the style of your bind calls, I assume named placeholders of the form :name, use whatever is the correct form for your odbc client library. I would be helpful to know what C++ client library you're using.

answered Mar 27, 2012 at 17:35

dbenhur

20.5k4 gold badges51 silver badges47 bronze badges

1 Comment

Nick Vaccaro Over a year ago

Thanks for the tip. I did forget to add the actual DboCommand object, which had the function call incorrectly specified. I've updated my code and the question, and posted the new error I'm getting.

Collectives™ on Stack Overflow

Calling a PL/PGSQL Function with Nullable Parameters

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related