Re: Clarity regarding the procedures call in postgresql for public and non-public schema

[Sasmit Utkarsh <utkarshsasmit@xxxxxxxxx>]

Hello Boris,

Please find the below snippets for sql_select_size_procedure

/** creation **/
                        res = PQexec(conn," CREATE OR REPLACE PROCEDURE sql_select_size_procedure(hexid text, rtp_in integer, INOUT size_data text
) LANGUAGE plpgsql AS $$ BEGIN SELECT size FROM riat WHERE id = hexid AND rtp = rtp_in INTO size_data; END; $$;");
                        LOG_DEBUG("%s() CREATE sql_select_size_procedure PQresultStatus = %s",__func__,PQresStatus(PQresultStatus(res)));
                        if(PQresultStatus(res) != PGRES_COMMAND_OK)
                        {
                                LOG_ERROR("CREATE sql_select_size_procedure failed! %s", PQerrorMessage(conn));
                                SQL_exit_nicely(conn,res);
                        }

 /**Calling 
sprintf(SelectSizeName,"%s","SelectSize");
 if(SQL_vsn10) {
         sprintf(SelectSizeCommand,"%s","SELECT size FROM riat WHERE id = $1 AND rtp = $2");
 } else {
         sprintf(SelectSizeCommand,"%s","CALL SQL_select_size_procedure($1, $2, NULL)");
 }
 SelectSizeNParams       = 2;
 SelectSizeParamTypes[0] = 25;  // {text}
 SelectSizeParamTypes[1] = 23;  // {int}

The point here I'm trying to make is that the same procedure is called with similar inputs in the earlier mail, But it is getting executed for 1 user i.e "pgddb_admin"(admin user) but not for shc_uadmin. 

Regards,

Sasmit Utkarsh

+91-7674022625

On Mon, Feb 26, 2024 at 6:24 PM Boris Zentner <bzm@xxxxxx> wrote:

You call the function with null as last argument. 

(SQL_select_size_procedure)But the function expect text. Either provide text or cast the null  like null::text. Or change the function input. 

--

Boris

Am 26.02.2024 um 13:27 schrieb Sasmit Utkarsh <utkarshsasmit@xxxxxxxxx>:

SQL_select_size_procedure