Using CFQUERY / CFSTOREDPROC to execute PostgreSQL

Hi ​@Eduardo31202118dymr 

Are any of the suggestions you’ve received of any help? Have you solved the problem? If so, could you please share your solution.

It will be useful to a fellow developer who arrives here with the same question.

I don’t have much experience with PostgreSQL. Nevertheless, I have a suggestion: just convert the procedure into a function, as you were advised.

That is, something like:
 

<!--- The query returns data --->
<cfquery name="getApp_Version" datasource="your_postgresql_dsn">

SELECT * FROM sp_app_version();

</cfquery>

or

<!--- The query does not return data (for example, an insert or update) --->
<cfquery name="getApp_Version" datasource="your_postgresql_dsn">

CALL sp_app_version();

</cfquery>

Also, since you are converting from mssql to postgres, this begs a few questions (which I hope may help you get resolution if no one else offers any).

1) First, when you say you “need to change the calls to the Stored Procedures”, do you mean that the postgres db you’re calling is already setup to offer stored procedures? or are you also converting mssql ones TO postgres (separate from the CFML code)?

I ask first because (as I have dug into this a bit more this morning) it seems that postgres regards sp’s differently than other db’s--it tends to favor use of postgres FUNCTIONS instead of STORED PROCEDURES when RETURNING data. (But there IS provision for a pg sp to define an “out” variable.)

2) This is why I ask if the things you’re calling in postgres are already written. And if so, are they really SP’s or might they be FUNCTIONS?  BTW, functions can be called with SELECT statements in plsql. You show using a CALL, which is indeed the plsql way to try to call an SP.

I’ll note that mssql also supported EXEC or CALL as ways to call an mssql SP using sql, and thus via cfquery. But often it was pretty trivial to change a simple SP call in cfquery to use cfstoredproc, where that may be helpful or more flexible.

3) Further, did you already have some code running against your postgres db and its SP that was running that way? Or did you perhaps find that as some example on the web of how to switch to calling an SP?

4) And when you show code doing a FETCH after the call, was that from some example you already had or found?  I wouldn’t think one would typically want to do “fetching” on the CF side, but different things you’re calling could demand different ways of processing them. Sometimes it may be worth questioning the design of that thing you’re calling.

5) Finally as for the COMMIT you show doing, some might wonder if that commit might be something that should do IN the SP….but again different DB designs work different ways (for better or worse).

I’ll note that CF offers cftransaction as a way to control whether some set of cfquery (or cfstoredproc) calls should themselves be treated as a “unit of work” which could be committed after they all complete (or could be rolled back).

Sorry if these are not simply “the answer” that you need.

The answer may be on the title of your post: you mention cfstoredproc, but you never show using it or trying it as an alternative to cfquery.

I'm not saying I've done any testing or research into any known issues with sp's in Pg. Maybe someone else will have more to say. But I wanted to start with this simple question for you first.

See the docs for cfstoredproc, for how easily you could call that sp, in just one line of code since you don't even need to pass in any arguments (using cfstoredprocparam), nor process multiple results (using cfprocresult).

FWIW, it is true that usually one can call an sp using cfquery, but sometimes cfstoredproc has advantages.