Hi Albe, this is code of my stored function:
CREATE OR REPLACE FUNCTION :FUNCTION_SCHEMA.get_deck_types
(
p_id_deck BIGINT
)
RETURNS BIGINT[] AS
$$
DECLARE
l_id_user BIGINT;
l_cards_number INTEGER;
l_deck_type BIGINT;
l_result BIGINT[];
BEGIN
SELECT INTO STRICT l_id_user id_user
FROM ccg_schema.decks_per_user
WHERE id = p_id_deck;
CREATE LOCAL TEMPORARY TABLE deck_types
ON COMMIT DROP
AS
SELECT stored_functions_v0.get_card_deck_types(t1.id_master_card) AS deck_type_ids
FROM ccg_schema.deck_composition T0
,ccg_schema.cards_per_user T1
WHERE id_deck = p_id_deck
AND t1.id_owner = l_id_user
AND t0.id_card = t1.id_card;
SELECT INTO l_cards_number COUNT(*)
FROM deck_types;
FOR l_deck_type IN SELECT DISTINCT unnest(deck_type_ids) FROM deck_types LOOP
IF (l_cards_number = (SELECT COUNT(*) FROM (SELECT unnest(deck_type_ids) AS id FROM deck_types) T0 WHERE id = l_deck_type)) THEN
l_result := array_append(l_result, l_deck_type);
END IF;
END LOOP;
RETURN l_result;
END;
$$
LANGUAGE PLPGSQL VOLATILE;
ConnectionPool reuse connections, of course, but how you can see from my code, the temporary table deck_types are already defined with ON COMMIT DROP clause, so I think that my work is not in transaction. Am I true? If so, how can I put my code in transaction? Many thanks. Mephysto
View this message in context:
Re: Temporary table already exists
Sent from the
PostgreSQL - general mailing list archive at Nabble.com.