Re: Temporary table already exists

[mephysto <mephystoonhell@xxxxxxxxx>]

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.