Error ошибка слишком много параметров для raise

I am trying to run my stored procedure scripts . There are 21 scripts amongst them 18 successfully executed but 3 of them are not executing. One of the script which is failing is given below (I am using PostgreSQL 9.5)

CREATE OR REPLACE FUNCTION create_temp_eid_table (tempPatientMappingTableName IN text ,errorMsg OUT text) 
RETURNS text AS $body$
BEGIN 
    EXECUTE 'create table ' ||  tempPatientMappingTableName || ' (
        ENCOUNTER_MAP_ID        varchar(200) NOT NULL,
        ENCOUNTER_MAP_ID_SOURCE     varchar(50) NOT NULL,
        PROJECT_ID              VARCHAR(50) NOT NULL,
        PATIENT_MAP_ID          varchar(200), 
        PATIENT_MAP_ID_SOURCE   varchar(50), 
        ENCOUNTER_ID            varchar(200) NOT NULL,
        ENCOUNTER_ID_SOURCE     varchar(50) ,
        ENCOUNTER_NUM           numeric, 
        ENCOUNTER_MAP_ID_STATUS    varchar(50),
        PROCESS_STATUS_FLAG     char(1),
        UPDATE_DATE timestamp, 
        DOWNLOAD_DATE timestamp, 
        IMPORT_DATE timestamp, 
        SOURCESYSTEM_CD varchar(50)
    ) WITH OIDS';
    EXECUTE 'CREATE INDEX idx_' || tempPatientMappingTableName || '_eid_id ON ' || tempPatientMappingTableName || '  (ENCOUNTER_ID, ENCOUNTER_ID_SOURCE, ENCOUNTER_MAP_ID, ENCOUNTER_MAP_ID_SOURCE, ENCOUNTER_NUM)';
EXECUTE 'CREATE INDEX idx_' || tempPatientMappingTableName || '_stateid_eid_id ON ' || tempPatientMappingTableName || '  (PROCESS_STATUS_FLAG)';  
EXCEPTION
WHEN OTHERS THEN
    RAISE NOTICE '%%%', SQLSTATE,  ' - ' , SQLERRM;
END;
$body$
LANGUAGE PLPGSQL;

i am getting error stating that

" org.postgresql.util.PSQLException: ERROR: too many parameters specified for RAISE 
Where: compilation of PL/pgSQL function "create_temp_eid_table" near line 23". 
Please check the image for clear error message.

Не относится конкретно к вашему вопросу, но вы можете исследовать строковые константы Postgres с долларовой котировкой. Это избавляет вас от необходимости использовать двойные кавычки в теле функции. Таким образом, ваша функция (изначально опубликованная вами становится):

CREATE OR REPLACE FUNCTION TABLA_MULT(numeric) RETURNS void AS $$
DECLARE 
texto1 TEXT := 'multiplicado por ';
texto2 TEXT := ' es igual a ';
BEGIN
RAISE NOTICE 'TABLA DE MULTIPLICAR DEL %',$1;
RAISE NOTICE '==========================';
FOR i IN 1..10 LOOP
DECLARE result numeric := ($1*i);
BEGIN
RAISE NOTICE 'El número %',i,texto1,$1,texto2,result;
END;
END LOOP;
END; $$ LANGUAGE 'plpgsql';

Также было бы выгодно систематически делать отступы в коде.

I am trying to run my stored procedure scripts . There are 21 scripts amongst them 18 successfully executed but 3 of them are not executing. One of the script which is failing is given below (I am using PostgreSQL 9.5)

CREATE OR REPLACE FUNCTION create_temp_eid_table (tempPatientMappingTableName IN text ,errorMsg OUT text) 
RETURNS text AS $body$
BEGIN 
    EXECUTE 'create table ' ||  tempPatientMappingTableName || ' (
        ENCOUNTER_MAP_ID        varchar(200) NOT NULL,
        ENCOUNTER_MAP_ID_SOURCE     varchar(50) NOT NULL,
        PROJECT_ID              VARCHAR(50) NOT NULL,
        PATIENT_MAP_ID          varchar(200), 
        PATIENT_MAP_ID_SOURCE   varchar(50), 
        ENCOUNTER_ID            varchar(200) NOT NULL,
        ENCOUNTER_ID_SOURCE     varchar(50) ,
        ENCOUNTER_NUM           numeric, 
        ENCOUNTER_MAP_ID_STATUS    varchar(50),
        PROCESS_STATUS_FLAG     char(1),
        UPDATE_DATE timestamp, 
        DOWNLOAD_DATE timestamp, 
        IMPORT_DATE timestamp, 
        SOURCESYSTEM_CD varchar(50)
    ) WITH OIDS';
    EXECUTE 'CREATE INDEX idx_' || tempPatientMappingTableName || '_eid_id ON ' || tempPatientMappingTableName || '  (ENCOUNTER_ID, ENCOUNTER_ID_SOURCE, ENCOUNTER_MAP_ID, ENCOUNTER_MAP_ID_SOURCE, ENCOUNTER_NUM)';
EXECUTE 'CREATE INDEX idx_' || tempPatientMappingTableName || '_stateid_eid_id ON ' || tempPatientMappingTableName || '  (PROCESS_STATUS_FLAG)';  
EXCEPTION
WHEN OTHERS THEN
    RAISE NOTICE '%%%', SQLSTATE,  ' - ' , SQLERRM;
END;
$body$
LANGUAGE PLPGSQL;

i am getting error stating that

" org.postgresql.util.PSQLException: ERROR: too many parameters specified for RAISE 
Where: compilation of PL/pgSQL function "create_temp_eid_table" near line 23". 
Please check the image for clear error message.

In this article, we will look into the Errors in that are inbuilt in PostgreSQL and the process of raising an error in PostgreSQL through RAISE statement and to use the ASSERT statement to insert debugging checks into PL/pgSQL blocks.

To raise an error message user can implement the RAISE statement as follows:

Syntax: RAISE level format;

Let’s explore into the raise statement a bit more. Following the RAISE statement is the level option that specifies the error severity. PostgreSQL provides the following levels:

• DEBUG
• LOG
• NOTICE
• INFO
• WARNING
• EXCEPTION

If users don’t specify the level, by default, the RAISE statement will use the EXCEPTION level that raises an error and stops the current transaction. We will discuss the RAISE EXCEPTION later in the next section.

The format is a string that specifies the message. The format uses percentage ( %) placeholders that will be substituted by the next arguments. The number of placeholders must match the number of arguments, otherwise, PostgreSQL will report the following error message:

[Err] ERROR:  too many parameters specified for RAISE

Example:

The following example illustrates the RAISE statement that reports different messages at the current time.

DO $$ 
BEGIN 
  RAISE INFO 'information message %', now() ;
  RAISE LOG 'log message %', now();
  RAISE DEBUG 'debug message %', now();
  RAISE WARNING 'warning message %', now();
  RAISE NOTICE 'notice message %', now();
END $$;

Output:

Note: Not all messages are reported back to the client, only INFO, WARNING, and NOTICE level messages are reported to the client. This is controlled by the client_min_messages and log_min_messages configuration parameters.

Raising Errors:

To raise errors, you use the EXCEPTION level after the RAISE statement. Note that the RAISE statement uses the EXCEPTION level by default. Besides raising an error, you can add more detailed information by using the following clause with the RAISE statement:

USING option = expression

The options can be any one of the below:

• MESSAGE: set error message text
• HINT: provide the hint message so that the root cause of the error is easier to be discovered.
• DETAIL:  give detailed information about the error.
• ERRCODE: identify the error code, which can be either by condition name or directly five-character SQLSTATE code.

Example 1:

DO $$ 
DECLARE
  email varchar(255) := 'raju@geeksforgeeks.org';
BEGIN 
  -- check email for duplicate
  -- ...
  -- report duplicate email
  RAISE EXCEPTION 'Duplicate email: %', email 
        USING HINT = 'Check the email again';
END $$;

Output:

Example 2:

The following examples illustrate how to raise an SQLSTATE and its corresponding condition:

DO $$ 
BEGIN 
    --...
    RAISE SQLSTATE '2210B';
END $$;
DO $$ 
BEGIN 
    --...
    RAISE invalid_regular_expression;
END $$;

Output:

Last Updated :
28 Aug, 2020

Like Article

Save Article

  l_task_source_id:= adapter.array_search('prps',g_status.ip_source_tables);
  l_check_stmt := ' select max(stufe::numeric) from '  || g_status.source_tables[l_task_source_id] ;
  execute     l_check_stmt   into l_max_hierarchy_level;  
  raise notice ' Max  hierarchy level : %',l_max_hierarchy_level;

Ваш ответ