CODEWITHSUNDEEP
sqlsql-serversql-server-2008postgresql
sighlent
sighlent

Reputation: 65

Return result of postgres function to SQL stored proc as a parameter

I want to query PostgreSQL database, which contains lists of info, for a simple count of current records from within MSSQL. The PG server is a linked server in MSSQL.

I know very little about Postgre but have enough to be dangerous. I created a simple query to reside inside a function as follows:

CREATE FUNCTION get_count(id text, division text, company integer, INOUT recCount integer) AS $$
BEGIN
recCount := (SELECT COUNT(0)
               FROM test.inventory
               WHERE inv_id = $1 AND div_code = $2 AND cmpy_no = $3);
END;
$$ LANGUAGE plpgsql;

I can query it all day long like this:

SET @Query = 'SELECT test.get_count(''' + @Id+ ''', ''' + @Div + ''', ' + CAST(@Company AS VARCHAR) +', ?);'
EXEC (@Query) AT POSTGRESQLTEST;

But I can't assign it to a variable like this:

SET @i = EXEC (@Query) AT POSTGRESQLPROD;
-- Error: Incorrect syntax near the keyword 'EXEC'.

Office mates say store the value in a temp table, query it, then drop it. Not very efficent IMHO but it will get the job done.

So what else can I do? How can I leverage the INOUT parameter recCount from the PG server?

Upvotes: 1

Views: 1932

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658312

Edited with feedback from comments below.
I think this should work with OpenQuery:

SELECT * FROM OpenQuery(POSTGRESQLTEST,
                'SELECT test.get_count(''some_inv_id'', ''some_div_code'', 123')

As @Andriy pointed out, OpenQuery does not accept variables. For quasi-dynamic SQL you have to assemble the whole query string before you can execute it. You'll have to double up single quotes around strings for that - twice!

Here is an example on .databasejournal.com how it can be done.
And @sighlent provided his solution below.

As an aside, concerning the plpgsql function:

You don't seem to need an input value for recCount. To simplify things a bit make that an OUT parameter instead of INOUT and you'll have one less input parameter:

CREATE OR REPLACE FUNCTION x.get_count(id text, division text, company integer
                                                       , OUT recCount integer) AS
$BODY$
BEGIN
    recCount := (
       SELECT COUNT(*)
       FROM   test.inventory
       WHERE  inv_id   = $1
       AND    div_code = $2
       AND    cmpy_no  = $3);
END;
$BODY$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions