PG_EXECUTE(n)

✨ PG_EXECUTE ✨

okay so this is the pg_execute page from the PostgreSQL Tcl Interface Documentation and honestly? it's a glow-up of a function. bestie, let's get into it 💅

---

🌸 SYNOPSIS 🌸

pg_execute [-array arrayVar] [-oid oidVar] conn commandString [procedure]

that's it. that's the whole vibe. two optional flags, a connection, a query, and maybe a callback if you're feeling fancy 🎀

---

💖 DESCRIPTION 💖

so pg_execute submits a command to the PostgreSQL server. that's the whole job. main character energy fr fr 🔥

if the command is not a SELECT statement, what comes back is the number of rows affected by the command. and ooh, special case: if the command is an INSERT statement and a single row is inserted, then the OID of the inserted row gets stored in the variable oidVar — but only if you passed the optional -oid argument. no flag, no OID, that's the deal 🙈

now if the command is a SELECT statement? this is where it gets good. for each row in the result:

• the row values get stored in the arrayVar variable (if you supplied it), using the column names as the array indices — cute, indexed, organized 💖
• otherwise they go into variables named by the column names (old school, still slaying)
• and then the optional procedure gets executed, if you supplied one ✨

side note bestie: omitting the procedure probably makes sense only if the query will return a single row. like, do the math. multiple rows + no procedure = you're just overwriting variables over and over 😭

the number of rows selected is what gets returned. iconic.

the procedure can use the Tcl commands break, continue, and return with the expected behavior — all the classics, working exactly how you'd expect 👑

⚠️ but watch out: if the procedure executes return, then pg_execute does not return the number of affected rows. the early exit ate the return value, no cap 💀

pg_execute is a newer function which provides a superset of the features of pg_select and can replace pg_exec in many cases where access to the result handle is not needed. so like, it's the upgraded version. the remake that actually slapped 🌈

now, errors — because we gotta talk about errors:

for server-handled errors, pg_execute will throw a Tcl error and return a two-element list. first element = an error code, such as PGRES_FATAL_ERROR. second element = the server error text. two for one 🫶

for more serious errors, such as failure to communicate with the server, pg_execute will throw a Tcl error and return just the error message text. no code, no list, just the raw text. that's the scary one 😭

---

🦋 ARGUMENTS 🦋

• -array arrayVar — specifies the name of an array variable where result rows are stored, indexed by the column names. 🍓 this gets ignored if commandString is not a SELECT statement — so don't come crying when nothing shows up.
• -oid oidVar — specifies the name of a variable into which the OID from an INSERT statement will be stored. very niche, very valid 💜
• conn — the handle of the connection on which to execute the command. your connection era 🔌
• commandString — the SQL command to execute. the actual query, the star of the show ⭐
• procedure — optional procedure to execute for each result row of a SELECT statement. run it per row, bestie, live your truth 🧚

---

👑 RETURN VALUE 👑

the number of rows affected or returned by the command. one number, very demure, very mindful 💅

---

🎀 EXAMPLES 🎀

in the following examples, error checking with catch has been omitted for clarity. (yes, unguarded. we're living dangerously 😌)

insert a row and save the OID in result_oid:

pg_execute -oid result_oid $pgconn "INSERT INTO mytable VALUES (1);"

print the columns item and value from each row:

pg_execute -array d $pgconn "SELECT item, value FROM mytable;" {
    puts "Item=$d(item) Value=$d(value)"
}

find the maximum and minimum values and store them in $s(max) and $s(min):

pg_execute -array s $pgconn "SELECT max(value) AS max, min(value) AS min FROM mytable;"

find the maximum and minimum values and store them in $max and $min:

pg_execute $pgconn "SELECT max(value) AS max, min(value) AS min FROM mytable;"

see the difference? no -array, no array — the column names become plain variables. it's giving "same query, different storage" and honestly both ate 🌸

---

that's the whole page, bestie — go query something iconic 💖✨

🫶 sign-off time: this lil masterpiece is dated 17 September 2020, from the PostgreSQL Tcl Interface Documentation. screenshot it, cite it, cite me, whatever — iykyk 💅