Skip to content

Commit eac7a68

Browse files
jdatcmdclaude
jdatcmd
and
claude
committed
Add spi_colnames/spi_coltypes/spi_coltypmods
Expose a SPI result's column metadata as parallel Arrays over its columns, the counterparts of PL/Python's colnames / coltypes / coltypmods: - spi_colnames(result) -> Array of column names (Strings) - spi_coltypes(result) -> Array of column type OIDs (Integers) - spi_coltypmods(result)-> Array of column type modifiers (Integers) Read from the result's tuple descriptor, skipping dropped columns. A result with no tuple set (a non-SELECT, e.g. a plain INSERT) has no columns, so each returns an empty Array -- consistent with spi_fetch_row, which already only yields rows for SELECT results. New sql/colmeta test; full suite green on PG 12 and 18. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
1 parent 50be762 commit eac7a68

7 files changed

Lines changed: 171 additions & 1 deletion

File tree

‎CHANGELOG.md‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,11 @@ and the project aims to follow [Semantic Versioning](https://semver.org/).
1414
PL/Python's `SD` (where `$_SHARED` is `GD`). Each function's `$_SD` is
1515
independent and resets when the function is recompiled; an anonymous `DO` block
1616
gets a fresh, empty `$_SD` each run.
17+
- **SPI result column metadata.** `spi_colnames(result)`, `spi_coltypes(result)`,
18+
and `spi_coltypmods(result)` return parallel `Array`s of a result's column
19+
names, type OIDs, and type modifiers, the counterparts of PL/Python's
20+
`colnames` / `coltypes` / `coltypmods`. A non-`SELECT` result returns empty
21+
`Array`s.
1722

1823
### Changed
1924

‎Makefile‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,7 @@ PG_CPPFLAGS = -I$(RUBY_ARCHHDRDIR) -I$(RUBY_HDRDIR)
3434
SHLIB_LINK = -L$(RUBY_LIBDIR) -L$(RUBY_ARCHLIBDIR) $(RUBY_LIBARG) $(RUBY_LIBS)
3535

3636
# Regression tests. "init" installs the extension; keep it first.
37-
REGRESS = init base types numspecial bytea composite encoding datetime jsonb misc variadic shared sd trigger trigger2 spi raise errors sqlstate errcontext cargs pseudo anycompat srf out varnames validator replace classes prepare cursor hostile compat txn evttrig subxact modules quote require cookbook stdio startproc oninit
37+
REGRESS = init base types numspecial bytea composite encoding datetime jsonb misc variadic shared sd trigger trigger2 spi colmeta raise errors sqlstate errcontext cargs pseudo anycompat srf out varnames validator replace classes prepare cursor hostile compat txn evttrig subxact modules quote require cookbook stdio startproc oninit
3838

3939
PG_CONFIG ?= pg_config
4040
PGXS := $(shell $(PG_CONFIG) --pgxs)

‎doc/comparison.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -37,6 +37,7 @@ Legend: ✅ supported · ➖ not applicable / different mechanism · ❌ not pro
3737
| Iterate rows | `spi_fetch_row` to `Hash`/`nil` | `spi_fetch_row` | `spi_fetchrow` | row loop / `-array` |
3838
| Stream via cursor | `spi_query`(+block)/`spi_fetchrow`/`spi_cursor_close`, `Cursor#each` | ➖ | `spi_query`/`spi_fetchrow`/`spi_cursor_close` | `spi_exec`/cursors |
3939
| Rows processed / status / rewind | `spi_processed`/`spi_status`/`spi_rewind` | ✅ same | ➖ (return hash) | ➖ |
40+
| Result column metadata | `spi_colnames`/`spi_coltypes`/`spi_coltypmods` | ➖ | ➖ | ➖ |
4041
| Prepare a plan | `spi_prepare(q, types...)` | ✅ same | `spi_prepare` | `spi_prepare` |
4142
| Execute a plan | `spi_exec_prepared` / `spi_query_prepared` | ✅ same | `spi_exec_prepared`/`spi_query_prepared` | `spi_execp` |
4243
| Free a plan | `spi_freeplan` | ✅ same | `spi_freeplan` | (auto) |

‎doc/plruby.md‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -339,8 +339,18 @@ Run queries against the current database from within a function:
339339
rows are exhausted.
340340
- `spi_processed(result)`: number of rows the query produced.
341341
- `spi_status(result)`: the SPI status code as a `String`.
342+
- `spi_colnames(result)`: an `Array` of the result's column names (`String`s).
343+
- `spi_coltypes(result)`: an `Array` of the columns' type OIDs (`Integer`s);
344+
cast one to a name with `oid::regtype` in SQL.
345+
- `spi_coltypmods(result)`: an `Array` of the columns' type modifiers
346+
(`Integer`s), e.g. `14` for `varchar(10)`, or `-1` when none applies.
342347
- `spi_rewind(result)`: restart iteration from the first row.
343348

349+
The three `spi_col*` accessors are the counterparts of PL/Python's `colnames`,
350+
`coltypes`, and `coltypmods`; each returns parallel `Array`s over the result
351+
columns. A result with no tuple set (a non-`SELECT`, such as a plain `INSERT`)
352+
has no columns, so each returns an empty `Array`.
353+
344354
```sql
345355
CREATE FUNCTION sum_series(n integer) RETURNS integer LANGUAGE plruby AS $$
346356
r = spi_exec("select generate_series(1, #{args[0]}) as g")

‎expected/colmeta.out‎

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
1+
--
2+
-- SPI result column metadata: spi_colnames / spi_coltypes / spi_coltypmods,
3+
-- the counterparts of PL/Python's colnames / coltypes / coltypmods. Each is a
4+
-- parallel Array over the result's columns; type OIDs are Integers.
5+
--
6+
-- A SELECT result exposes its column names, type OIDs, and type modifiers.
7+
-- (int4 = 23, text = 25, varchar = 1043; varchar(10) typmod = 14 = 10 + 4.)
8+
CREATE FUNCTION colmeta() RETURNS text LANGUAGE plruby AS $$
9+
r = spi_exec("SELECT 1::int AS id, 'hi'::text AS label, 'ab'::varchar(10) AS code")
10+
"names=#{spi_colnames(r).inspect} " +
11+
"types=#{spi_coltypes(r).inspect} " +
12+
"typmods=#{spi_coltypmods(r).inspect}"
13+
$$;
14+
SELECT colmeta();
15+
colmeta
16+
-------------------------------------------------------------------------
17+
names=["id", "label", "code"] types=[23, 25, 1043] typmods=[-1, -1, 14]
18+
(1 row)
19+
20+
-- The OIDs are usable: map them back to type names through the catalog.
21+
CREATE FUNCTION colmeta_typenames() RETURNS text[] LANGUAGE plruby AS $$
22+
r = spi_exec("SELECT 1::int AS id, 'ab'::varchar(10) AS code")
23+
spi_coltypes(r).map do |oid|
24+
t = spi_exec("SELECT #{oid}::regtype::text AS n")
25+
spi_fetch_row(t)['n']
26+
end
27+
$$;
28+
SELECT colmeta_typenames();
29+
colmeta_typenames
30+
-------------------------------
31+
{integer,"character varying"}
32+
(1 row)
33+
34+
-- A non-SELECT (no tuple table) has no columns: each metadata Array is empty.
35+
CREATE FUNCTION colmeta_ddl() RETURNS text LANGUAGE plruby AS $$
36+
spi_exec("CREATE TEMP TABLE t_cm(x int)")
37+
r = spi_exec("INSERT INTO t_cm VALUES (1)")
38+
"names=#{spi_colnames(r).inspect} " +
39+
"types=#{spi_coltypes(r).inspect} " +
40+
"processed=#{spi_processed(r)}"
41+
$$;
42+
SELECT colmeta_ddl();
43+
colmeta_ddl
44+
-------------------------------
45+
names=[] types=[] processed=1
46+
(1 row)
47+

‎plruby_spi.c‎

Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -477,6 +477,72 @@ plruby_spi_status(VALUE self, VALUE res)
477477
return rb_str_new_cstr(SPI_result_code_string(r->status));
478478
}
479479

480+
/*
481+
* Column metadata for a result's tuple descriptor, as parallel Arrays over the
482+
* (non-dropped) result columns -- the counterparts of PL/Python's colnames /
483+
* coltypes / coltypmods. A result without a tuple table (a non-SELECT, like a
484+
* plain INSERT) has no columns, so each returns an empty Array.
485+
*/
486+
typedef enum
487+
{
488+
PLRUBY_COL_NAME,
489+
PLRUBY_COL_TYPE,
490+
PLRUBY_COL_TYPMOD
491+
} plruby_col_kind;
492+
493+
static VALUE
494+
plruby_spi_colmeta(VALUE res, plruby_col_kind kind)
495+
{
496+
plruby_spi_result *r = plruby_get_spi_result(res);
497+
VALUE out = rb_ary_new();
498+
TupleDesc tupdesc;
499+
int i;
500+
501+
if (r->tuptable == NULL)
502+
return out;
503+
504+
tupdesc = r->tuptable->tupdesc;
505+
for (i = 0; i < tupdesc->natts; i++)
506+
{
507+
Form_pg_attribute att = TupleDescAttr(tupdesc, i);
508+
509+
if (att->attisdropped)
510+
continue;
511+
512+
switch (kind)
513+
{
514+
case PLRUBY_COL_NAME:
515+
rb_ary_push(out, rb_str_new_cstr(NameStr(att->attname)));
516+
break;
517+
case PLRUBY_COL_TYPE:
518+
rb_ary_push(out, UINT2NUM(att->atttypid));
519+
break;
520+
case PLRUBY_COL_TYPMOD:
521+
rb_ary_push(out, INT2NUM(att->atttypmod));
522+
break;
523+
}
524+
}
525+
return out;
526+
}
527+
528+
static VALUE
529+
plruby_spi_colnames(VALUE self, VALUE res)
530+
{
531+
return plruby_spi_colmeta(res, PLRUBY_COL_NAME);
532+
}
533+
534+
static VALUE
535+
plruby_spi_coltypes(VALUE self, VALUE res)
536+
{
537+
return plruby_spi_colmeta(res, PLRUBY_COL_TYPE);
538+
}
539+
540+
static VALUE
541+
plruby_spi_coltypmods(VALUE self, VALUE res)
542+
{
543+
return plruby_spi_colmeta(res, PLRUBY_COL_TYPMOD);
544+
}
545+
480546
static VALUE
481547
plruby_spi_rewind(VALUE self, VALUE res)
482548
{
@@ -1277,6 +1343,12 @@ plruby_spi_init(void)
12771343
RUBY_METHOD_FUNC(plruby_spi_processed), 1);
12781344
rb_define_global_function("spi_status",
12791345
RUBY_METHOD_FUNC(plruby_spi_status), 1);
1346+
rb_define_global_function("spi_colnames",
1347+
RUBY_METHOD_FUNC(plruby_spi_colnames), 1);
1348+
rb_define_global_function("spi_coltypes",
1349+
RUBY_METHOD_FUNC(plruby_spi_coltypes), 1);
1350+
rb_define_global_function("spi_coltypmods",
1351+
RUBY_METHOD_FUNC(plruby_spi_coltypmods), 1);
12801352
rb_define_global_function("spi_rewind",
12811353
RUBY_METHOD_FUNC(plruby_spi_rewind), 1);
12821354

‎sql/colmeta.sql‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,35 @@
1+
--
2+
-- SPI result column metadata: spi_colnames / spi_coltypes / spi_coltypmods,
3+
-- the counterparts of PL/Python's colnames / coltypes / coltypmods. Each is a
4+
-- parallel Array over the result's columns; type OIDs are Integers.
5+
--
6+
7+
-- A SELECT result exposes its column names, type OIDs, and type modifiers.
8+
-- (int4 = 23, text = 25, varchar = 1043; varchar(10) typmod = 14 = 10 + 4.)
9+
CREATE FUNCTION colmeta() RETURNS text LANGUAGE plruby AS $$
10+
r = spi_exec("SELECT 1::int AS id, 'hi'::text AS label, 'ab'::varchar(10) AS code")
11+
"names=#{spi_colnames(r).inspect} " +
12+
"types=#{spi_coltypes(r).inspect} " +
13+
"typmods=#{spi_coltypmods(r).inspect}"
14+
$$;
15+
SELECT colmeta();
16+
17+
-- The OIDs are usable: map them back to type names through the catalog.
18+
CREATE FUNCTION colmeta_typenames() RETURNS text[] LANGUAGE plruby AS $$
19+
r = spi_exec("SELECT 1::int AS id, 'ab'::varchar(10) AS code")
20+
spi_coltypes(r).map do |oid|
21+
t = spi_exec("SELECT #{oid}::regtype::text AS n")
22+
spi_fetch_row(t)['n']
23+
end
24+
$$;
25+
SELECT colmeta_typenames();
26+
27+
-- A non-SELECT (no tuple table) has no columns: each metadata Array is empty.
28+
CREATE FUNCTION colmeta_ddl() RETURNS text LANGUAGE plruby AS $$
29+
spi_exec("CREATE TEMP TABLE t_cm(x int)")
30+
r = spi_exec("INSERT INTO t_cm VALUES (1)")
31+
"names=#{spi_colnames(r).inspect} " +
32+
"types=#{spi_coltypes(r).inspect} " +
33+
"processed=#{spi_processed(r)}"
34+
$$;
35+
SELECT colmeta_ddl();

0 commit comments

Comments
 (0)