Q. Support for anonymous code block execution? DO $$ ... END $$

[hexnaught]

I am getting the following error and I am unsure as to why, I can only assume that pgx/pgxpool is attempting to expand $$ in the below as an argument even though it isn't one? Is this a limitation of the lib or me not using the lib properly?

The error I am getting back is:

mismatched param and argument count

I have the following query that works fine and as intended when ran against our postgres instance directly. I have renamed the tables and some fields to leave out our domain info, forgive any small alias errors.

DO $$ BEGIN
	IF EXISTS (
		WITH latest_eligibility AS (
			SELECT user_id, max(created_at) as created_at
			FROM eligibility
			WHERE user_id = '692e2693-06c4-487f-906e-13a2a6708c55'
			GROUP BY user_id
		)
		SELECT u_el.id, u_el.sc_id, u_el.eligible
		FROM latest_eligibility el
		JOIN eligibility u_el ON
			u_el.sc_id = el.sc_id
			AND u_el.created_at = el.created_at
		WHERE eligible <> false
	)
	THEN
		INSERT INTO eligibility (user_id, eligible)
			VALUES ('692e2693-06c4-487f-906e-13a2a6708c55', false);
	END IF;
END $$;

Within our GO code we have the following:

func (s *Service) SetEligibility(ctx context.Context, userID string, value bool) error {
	query := `
		DO $$ BEGIN
			IF EXISTS (
				WITH latest_eligibility AS (
					SELECT user_id, max(created_at) as created_at
					FROM eligibility
					WHERE user_id = $1
					GROUP BY user_id
				)
				SELECT u_el.id, u_el.sc_id, u_el.eligible
				FROM latest_eligibility el
				JOIN eligibility u_el ON
					u_el.sc_id = el.sc_id
					AND u_el.created_at = el.created_at
				WHERE eligible <> $2
			)
			THEN
				INSERT INTO eligibility (user_id, eligible)
					VALUES ($1, $2);
			END IF;
		END $$;
	`

	// s.Pool is a *pgxpool.Pool
	_, err := s.Pool.Exec(ctx, query, userID, value)
	// err == "mismatched param and argument count"
	
	return errors.Wrapf(err, "setting eligibility for userID: %s", userID)
}

[jackc]

This is a limitation of the PostgreSQL. You cannot have arguments (e.g. $1, $2) inside of a DO block that come from outside. As far as PostgreSQL is concerned there are no arguments.

[hexnaught]

Aah okay, thank you!