PG_EXEC(n)

💅 pg_exec — like, the whole Tcl-side PostgreSQL command sender, from the PostgreSQL Tcl Interface Documentation 🎀

okay bestie, sit down, hydrate, and let me tell you about pg_exec — the little Tcl command that walks your SQL straight up to the PostgreSQL server and comes back with a result. You're gonna slay this, I promise. 🦋

✨ SYNOPSIS ✨

pg_exec [-paramarray arrayVar] [-variables] conn commandString [args]

💖 DESCRIPTION 💖

So pg_exec submits a command to the PostgreSQL server and hands you back a result. And the result handles? Super cute detail: they start with the connection handle, then a period, then a result number. Like conn.0, conn.1 — it's giving "I got numbered receipts." 🧾✨

⚠️ MAJOR vibe check, do not skip this one: the absence of a Tcl error is not proof that your command succeeded! Nope nope nope. An error message coming back from the server gets processed as a command result with failure status — it does not generate a Tcl error inside pg_exec. So you literally have to check for {[pg_result $result -status] == PGRES_COMMAND_OK}. That's the little test that tells you the truth. Do it. Every time. 🙏 (yes this is me being dramatic, and yes I mean it 😭)

Now, if you pass the [-paramarray] flag, a substitution happens on your query — and it does it securely — replacing each back-quote delimited name with the corresponding entry from the named array. And if the array doesn't contain the named element? Then NULL is substituted, which is basically how an array created by -withoutnulls gets generated anyway. Just so you know: each of those names has to occur in a location where a value or a field name could actually appear. For more info, peep pg_select. 💜

And if you pass the [-variables] flag, embedded Tcl variables get passed as parameters. You can flag variables with ':', and array references and namespaces are supported. Complex variable names can usually be embedded with :{nasty-name-here} — though no attempt at duplicating Tcl brace-escaping is made, fyi. And if the variable doesn't exist? Boom: NULL is substituted. 🫶

🌸 ARGUMENTS 🌸

• [-paramarray arrayname] — performs parameter substitution via `quoted` elements of the array using PQexecParams or PQSendQueryParams. It's the secure-glow-up option, honestly. ✨
• [-variables] — substitutes Tcl variables found in the SQL string using PQexecParams or PQSendQueryParams. Bestie, it just gets your variables. 💅
• conn — the handle of the connection on which to execute the command. This is your ride. 🚗
• commandString — the SQL command to execute. The actual main character here. 👑
• args — for PostgreSQL versions greater than 7.4, args consists of zero or more optional values that can be inserted, unquoted, into the SQL statement using $-style substitution. Nulls are represented by the string "NULL". Yes, the literal string. Not the SQL thing. The string. 💀

⭐ RETURN VALUE ⭐

You get a result handle back. A Tcl error will be returned if pgtcl was unable to obtain a server response — that's the only case where you get the Tcl error treatment. Otherwise, a command result object is created and a handle for it is returned, and you can hand that handle straight to pg_result to obtain the results of the command. Chain it. Pass it. Slay. 🔥

🌈 EXAMPLE 🌈

pg_exec $conn {select * from table1 where id = $1 and user = $2} $id $user

look at that. $1 and $2 getting filled in with $id and $user. Iconic behaviour, no notes. 🎀

and that's the whole page, bestie — you ate. 🍓

this one's from the PostgreSQL Tcl Interface Documentation, dated 17 September 2020. go forth and pg_exec responsibly. 💜✨