NpgsqlRest

Appearance

Comments
Written with Claude
IMPORTANT

As you may notice, this page and pretty much the entire website were obviously created with the help of AI. I wonder how you could tell? Was it a big "Written With Claude" badge on every page? I moved it to the top now (with the help of AI of course) to make it even more obvious. There are a few blogposts that were written by me manually, the old-fashioned way, I hope there will be more in the future, and those have a similar "Human Written" badge. This project (not the website), on the other hand, is a very, very different story. It took me more than two years of painstaking and unpaid work in my own free time. A story that, hopefully, I will tell someday. But meanwhile, what would you like me to do? To create a complex documentation website with a bunch of highly technical articles with the help of AI and fake it, to give you an illusion that I also did that manually? Like the half of itnernet is doing at this point? How does that makes any sense? Is that even fair to you? Or maybe to create this website manually, the old-fashioned way, just for you? While working a paid job for a salary, most of you wouldn't even get up in the morning. Would you like me to sing you a song while we're at it? For your personal entertainment? Seriously, get a grip. Do you find this information less valuable because of the way this website was created? I give my best to fix it to keep the information as accurate as possible, and I think it is very accurate at this point. If you find some mistakes, inaccurancies or problems, there is a comment section at the bottom of every page, which I also made with the help of the AI. And I woould very much appreciate if you leave your feedback there. Look, I'm just a guy who likes SQL, that's all. If you don't approve of how this website was constructed and the use of AI tools, I suggest closing this page and never wever coming back. And good riddance. And I would ban your access if I could know how. Thank you for your attention to this matter.

RETURNS ​

Skip the PostgreSQL Describe step for a statement and resolve return columns from a composite type instead. This is a positional annotation — it applies to the next statement below it.

Available since version 3.12.0.

Syntax ​

code
@returns <composite_type_name>
@returns <scalar_type>
@returns void

Supported values:

  • Composite type name — schema-qualified (e.g., public.my_type) or unqualified (e.g., my_type). Columns resolved from the type definition.
  • Scalar type — any built-in PostgreSQL type (e.g., integer, text, boolean, jsonb). Declares a single-column result. Only the first column from the query is used at runtime.
  • void — no columns, no results.

This annotation skips the PostgreSQL Describe step entirely for the annotated statement. The statement's SQL is never sent to PostgreSQL during startup.

When to Use ​

  • Composite types: when the statement references objects that don't exist at startup (e.g., temp tables created inside DO blocks)
  • Scalar types: when you want to declare a single typed return value and ignore extra columns
  • void: when the statement returns no results (e.g., INSERT, CREATE TEMP TABLE, set_config)

Example ​

sql
sql
-- HTTP GET
-- @param $1 val1 text
-- @param $2 val2 integer
begin;
select set_config('app.val1', $1, true); -- @skip
select set_config('app.val2', $2::text, true); -- @skip
do $$ begin
    create temp table _result on commit drop as
    select current_setting('app.val1') as val1,
           current_setting('app.val2')::int as val2,
           true as active;
end; $$;
-- @returns my_result_type
-- @result data
-- @single
select * from _result;
end;

Where my_result_type is defined as:

sql
sql
create type my_result_type as (
    val1 text,
    val2 integer,
    active boolean
);

Without @returns, the select * from _result statement fails at startup because the temp table doesn't exist yet. With @returns my_result_type, the columns are resolved from the composite type definition in pg_catalog.

Scalar Type ​

Declare a single typed column. Only the first column from the query is used at runtime — extra columns are ignored:

sql
sql
-- HTTP GET
-- @returns integer
select count(*) from users;

Returns: [42]

With @single, returns a bare scalar value:

sql
sql
-- HTTP GET
-- @returns integer
-- @single
select count(*) from users;

Returns: 42

Supported scalar types: integer, text, boolean, jsonb, json, bigint, numeric, real, double precision, date, timestamp, timestamptz, uuid, bytea, and all other built-in PostgreSQL types.

Void Statements ​

Use @returns void to skip Describe for statements that return no results:

sql
sql
-- HTTP POST
-- @param $1 key text
-- @param $2 value text
-- @returns void
select set_config($1, $2, false);
-- @result data
select current_setting($1, true) as result;

The first statement's Describe is skipped entirely. In multi-command files, it produces a rows-affected count in the response. For single-command files, it makes the endpoint void (returns 204 No Content).

Behavior ​

  • The Describe step is skipped entirely for annotated statements — the SQL is never sent to PostgreSQL during startup
  • For composite types: the type must exist in the database at startup. If not found, an error is logged and the file is skipped or exits (depending on ErrorMode)
  • For void: the statement is treated as returning no columns (zero-column result)
  • No parameter type inference happens for the skipped statement — other statements in the same multi-command file provide parameter types
  • At runtime, the actual query result must match the declared type's column structure — mismatches may produce incorrect output
  • Can be combined with other positional annotations like @result, @single, @skip

@returns void vs @void

For single-command SQL files, @returns void has the same runtime effect as @void — both return 204 No Content. The difference: @returns void skips the Describe step (the SQL is never sent to PostgreSQL at startup), while @void still runs Describe and only changes the runtime response. Use @returns void when the statement would fail Describe (e.g., references a temp table). Use @void when Describe succeeds but you don't want any response.

Comments