how do you declare a table type in plpgsql? I see %rowtype but if i want to

select * from todos where todo.done = true into sub_todos;

its just a filtered table but I dont think

declare
sub_todos public.todos%type;

is a thing. Or

declare
sub_todos public.todos%rowtype[];

About to check out for the night, and wanted to throw something at you as weekends tend to be slower here. I assume you are not wanting to return a "custom" table type, but need an intermediate table. Maybe this: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-temporary-table/ If that is not what you are looking for you might ask again in a bit as once a thread starts, views on it go down.

Ill check it out thanks!

I don't know about my previous response based on this: https://stackoverflow.com/questions/34669943/how-to-declare-and-use-a-temporary-table-as-a-cursor-source-in-postgresql . I've not done it and you might want better advice. If you want to return a custom table this is the way: https://www.geeksforgeeks.org/postgresql-function-returning-a-table/ but I don't think that is what you are asking. Obviously Supabase is Postgres so Google is not limited to Supabase answers.

yeah i may just not be used to how to do things in plpgsql coming from js yet. Basically want to pass json to a function similar to graphql and then convert into tables for a blogpost that has children (and have checks in func like make sure not too many children elements) so for a create/edit will use json then convert to jsonb (apparently its better so sure). btw are

json_extract_path(blogpost,'{id}')

and blogpost->>id the same? still going kinda line by line on errors from my js thinking draft but was thinking put all children where blogpost_id = blogpost->>id into declare var then turn jsonb children to be into rows as well into a declare var loop through new ones and if id exists in current list then update that id. If it doesnt exist create it. loop through og list if an id no longer exists in new one delete it

If you are trying to return a table as the result in a function then the Returning a Table article is probably what you want. You define the table columns, then your logic loops thru building rows in that table filling in the columns in each row, then you return the table. You use it when you want to add extra columns to rows and can't use setof rowtype because it must match the table columns exactly in the original table.