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.