Zenn
Open1

PostgreSQLのTIPS

PostgreSQL
青柳康平青柳康平

PERFORMのFOUNDを検証。SELECTで行を返せばTRUEになる。FROMのあり無しは関係無い

DROP TYPE IF EXISTS type_sample_set_test CASCADE;
CREATE TYPE type_sample_set_test AS (
  code TEXT
);

CREATE OR REPLACE FUNCTION sample_set_test(
  p_flag BOOLEAN
) RETURNS SETOF type_sample_set_test AS $FUNCTION$
DECLARE
BEGIN
  IF p_flag THEN
    RETURN QUERY SELECT 'x';
  END IF;
END;
$FUNCTION$ LANGUAGE plpgsql;

DROP TYPE IF EXISTS type_sample_set_test2 CASCADE;
CREATE TYPE type_sample_set_test2 AS (
  code TEXT
);

CREATE OR REPLACE FUNCTION sample_set_test2(
) RETURNS SETOF type_sample_set_test2 AS $FUNCTION$
DECLARE
BEGIN
  PERFORM * FROM sample_set_test(p_flag := TRUE);
  IF FOUND THEN
    RETURN QUERY SELECT 'a';
  ELSE
    RETURN QUERY SELECT 'b';
  END IF;

  PERFORM sample_set_test(p_flag := TRUE);
  IF FOUND THEN
    RETURN QUERY SELECT 'a';
  ELSE
    RETURN QUERY SELECT 'b';
  END IF;

  PERFORM * FROM sample_set_test(p_flag := FALSE);
  IF FOUND THEN
    RETURN QUERY SELECT 'a';
  ELSE
    RETURN QUERY SELECT 'b';
  END IF;

  PERFORM sample_set_test(p_flag := FALSE);
  IF FOUND THEN
    RETURN QUERY SELECT 'a';
  ELSE
    RETURN QUERY SELECT 'b';
  END IF;
END;
$FUNCTION$ LANGUAGE plpgsql;

elect * from sample_set_test2();
 code 
------
 a
 a
 b
 b