Re: Temporary table already exists

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.