Postgresql ColdFusion CFQuery Block

Question

I am new to ColdFusion, I have written a block of Postgres code in CFQuery :

<cffunction name="insertToReport" access="private" returntype="struct" output="false" >

  <cfset var xyz = structNew() >
  <cfset xyz.code = "">
      <cfquery name="querysampleresult" datasource="abc">
            DO            
            $BODY$
                DECLARE resultValue int;
            BEGIN
               resultValue = 1;
               SELECT resultValue INTO resultValue ;    
            END;
            $BODY$
      </cfquery>

            <cfset xyz.code = querysampleresult.resultValue  >

           <cfreturn xyz >
</cffunction>

My problem is that I am unable to access the variable resultValue outside of the CFQuery tag, i.e it is throwing the exception:

Element RESULTVALUE is undefined in querysampleresult

This is occurring at the CFSet statement at the end of function here:

<cfset xyz.code = querysampleresult.resultValue  >

I don't know how I can set the variable resultValue in the structure and I am bound to return a structure from here to the calling environment. Please help me in this, Thanks In Advance.

Answer 1

An anonymous code block (DO) always returns void. You need to use a function to return anything else:

create function f()
returns integer as $body$
declare 
    resultValue integer;
begin
    resultValue := 1;
    return resultValue;
end;
$body$
language plpgsql

After the function is created you can use it in your query:

select f() as resultValue;

Answer 2

Add another select statement into the query that selects the result value. Right now you're selecting INTO a table, which doesn't return a query.

<cffunction name="insertToReport" access="private" returntype="struct" output="false" >
<cfargument name="rptDataObj" required="yes" type="com.certain.register123.data.reportData" hint="The affected report.">
<cfargument name="maxColumns" type="numeric" required="false" default="10" hint="The maximum number of active columns that should be saved to the report. " ><!--- As of REG-559 20060215, the default was 10 --->
<cfargument name="dsn" type="string" required="false" default="#application.portals.data[request.applicationName].dsn#" >
<cfset var uiCustomColumn = queryNew("") >
<cfset var strSaveError = structNew() >
<cfset strSaveError.code = 0 >
<cfset strSaveError.message = "">
<cfset strSaveError.udcId = "">


<cfquery name="uiCustomColumn" datasource="#arguments.dsn#">
DO            
$BODY$
    DECLARE resultValue int;
    DECLARE nextId bigint;
    DECLARE maxColumns bigint;
    DECLARE udcReportId bigint; /*Have to use this value more than once, so declare it to reduce number of parameters*/
    DECLARE udcOrder int;  /*Have to use this value more than once, so declare it to reduce number of parameters*/

BEGIN
    udcReportId = #arguments.rptDataObj.getId()# ;
    maxColumns = #arguments.maxColumns# ;

    IF (( select count( udc_id ) from user_defined_column WHERE udc_frn_rpt_id = udcReportId AND udc_is_active = true ) >= maxColumns) THEN
        BEGIN
            resultValue = 1; /*There isn't an available slot for this column */
        END;
        ELSE
        BEGIN

            nextId = (SELECT coalesce( MAX(udc_id), 0 ) FROM user_defined_column ) + 1 ;
            udcOrder = (SELECT coalesce( MAX(udc_order), 0 ) FROM user_defined_column WHERE udc_frn_rpt_id = udcReportId AND udc_is_active = true ) + 1 ;

            INSERT INTO user_defined_column(
                udc_id
                ,udc_frn_rpt_id
                ,udc_label
                ,udc_data
                ,udc_type
                ,udc_order
                ,udc_is_active
                ,udc_date_created
                ,udc_date_modified
            )
            VALUES(
                nextId
                ,udcReportId
                ,'hi'
                ,'hi'
                ,12
                ,udcOrder
                ,true
                ,now()
                ,now()
            );


            resultValue = 0;
        END ;
    END IF;
    SELECT resultValue, nextId INTO resultValue  /*Set a success result */
          , nextId;

    SELECT resultValue;

END;
$BODY$
</cfquery>

<cfset strSaveError.code = uiCustomColumn.resultValue  >
<cfset strSaveError.udcId = uiCustomColumn.nextId  >

<cfreturn strSaveError >