CODEWITHSUNDEEP
sqlpostgresql
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5246

Why function don't return boolean? (Query has no destination for result data)

In PostgresSQL database I have such function:

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;

When I try to call this function it raise error and don't return boolean result.

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

There is how I call this function:

SELECT recreate(ARRAY['QUESTION 1','QUESTION 2','QUESTION 3'], ARRAY[1,1,1], ARRAY[false,false,false], ARRAY[0,1,2]);

Upvotes: 0

Views: 58

Answers (1)

user330315
user330315

Reputation:

In PL/pgSQL the result of a query needs to be stored somewhere - and your code doesn't do that. You need to either use return query or simply get rid of the final SELECT in the CTE chain and use 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;

Upvotes: 2

Related Questions