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

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(at)2bz(dot)de> 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(at)gmail(dot)com>:
>
> SQL_select_size_procedure
>
>