En la base de datos PostgresQL tengo tal función :

CREATE OR REPLACE FUNCTION recreate (
    TEXT_ARRAY TEXT[],
    WIDGET_ARRAY INT[],
    REQUIRED_ARRAY BOOLEAN[],
    POSITION_ARRAY INT[]
) RETURNS BOOLEAN AS $$
    BEGIN
        WITH NEW_QUESTIONS AS (
            INSERT INTO QUESTIONS (TEXT, WIDGET, REQUIRED, POSITION, CATEGORY) 
            SELECT 
                UNNEST(ARRAY[TEXT_ARRAY]) AS TEXT,
                UNNEST(ARRAY[WIDGET_ARRAY]) AS WIDGET,
                UNNEST(ARRAY[REQUIRED_ARRAY]) AS REQUIRED,
                UNNEST(ARRAY[POSITION_ARRAY]) AS POSITION,
                2 AS CATEGORY
            RETURNING ID
        ),
        GENERATE_QUESTIONS_OPTIONS_RELATIONSHIP AS (
            INSERT INTO QUESTIONS_OPTIONS_RELATIONSHIP (QUESTION_ID, OPTION_ID)
            SELECT ID, UNNEST(ARRAY[1, 2, 3, 4, 5, 6]) AS OPTION_ID FROM NEW_QUESTIONS
            ON CONFLICT ON CONSTRAINT QUESTIONS_OPTIONS_RELATIONSHIP_UNIQUE_KEY DO NOTHING
        ) SELECT TRUE;
    END;
$$ LANGUAGE plpgsql;

Cuando intento llamar a esta función, genera un error y no devuelve un resultado booleano.

ERROR :

SQL Error [42601]: ERROR: query has no destination for result data
If you want to discard the results of a SELECT, use PERFORM instead.
PL/pgSQL function recreate(text[],integer[],boolean[],integer[]) line 3 at SQL statement

Así es como llamo a esta función:

SELECT recreate(ARRAY['QUESTION 1','QUESTION 2','QUESTION 3'], ARRAY[1,1,1], ARRAY[false,false,false], ARRAY[0,1,2]);
0
Nurzhan Nogerbek 8 oct. 2019 a las 13:37

1 respuesta

La mejor respuesta

En PL / pgSQL, el resultado de una consulta debe almacenarse en algún lugar, y su código no hace eso. Necesita usar return query o simplemente deshacerse del SELECT final en la cadena CTE y usar return:

CREATE OR REPLACE FUNCTION recreate (
    text_array text[],
    widget_array int[],
    required_array boolean[],
    position_array int[]
) RETURNS boolean 
AS $$
begin
  WITH new_questions AS (
      INSERT INTO questions (text, widget, required, position, category) 
      SELECT 
          UNNEST(ARRAY[text_array]) AS text,
          UNNEST(ARRAY[widget_array]) AS widget,
          UNNEST(ARRAY[required_array]) AS required,
          UNNEST(ARRAY[position_array]) AS position,
          2 AS category
      RETURNING id
  )
  INSERT INTO questions_options_relationship (question_id, option_id)
  SELECT id, UNNEST(ARRAY[1, 2, 3, 4, 5, 6]) AS option_id 
  FROM new_questions
  ON CONFLICT ON CONSTRAINT questions_options_relationship_unique_key DO NOTHING;

  RETURN true;
END; 
$$ 
LANGUAGE plpgsql;
2
a_horse_with_no_name 8 oct. 2019 a las 10:50