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

Question

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?

Erwin Brandstetter · Accepted Answer · 2012-04-28 13:08:26Z

1

^{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;

edited Apr 28, 2012 at 13:08

answered Apr 27, 2012 at 16:51

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Andriy M Over a year ago

OPENQUERY's second parameter must be just a string, it cannot be an expression. (Which means that you can't parametrise it directly, and if you want to parametrise it, you'll need to make it a dynamic query.)

sighlent Over a year ago

That solved it.

SET	@Query = N'SELECT test.get_count(''' + @Id + ''', ''' + @Div + ''', ' + CAST(@Company AS VARCHAR) +', 0);'     SET	@Query = N'SELECT @RecCount = get_count FROM OPENQUERY(PostgreSQLPROD, ''' + REPLACE(@Query, '''', '''''') + ''')'     --PRINT @Query 	EXECUTE sp_executesql 		@Query 		,N'@RecCount int OUTPUT' 		,@RecCount = @i OUTPUT;  	SELECT @i

That's how it's done. Thanks folks!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related