Before you go there: for only few, known tables names, it’s typically simpler to avoid dynamic SQL and spell out the few code variants in separate functions or in a CASE construct. That said, what you are trying to achieve can be simplified and improved: CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer) LANGUAGE … Read more
There are two ways. One is to aggregate: SELECT array_agg(column_name::TEXT) FROM information.schema.columns WHERE table_name=”aean” The other is to use an array constructor: SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name=”aean” ) I’m presuming this is for plpgsql. In that case you can assign it like this: colnames := ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name=”aean” … Read more
These dollar signs ($$) are used for dollar quoting, which is in no way specific to function definitions. It can be used to replace single quotes enclosing string literals (constants) anywhere in SQL scripts. The body of a function happens to be such a string literal. Dollar-quoting is a PostgreSQL-specific substitute for single quotes to … Read more
Use RETURN QUERY: CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int) RETURNS TABLE (txt text — also visible as OUT param in function body , cnt bigint , ratio bigint) LANGUAGE plpgsql AS $func$ BEGIN RETURN QUERY SELECT t.txt , count(*) AS cnt — column alias only visible in this query , (count(*) * 100) / _max_tokens … Read more
Use RETURNS void like below: CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ #variable_conflict use_variable DECLARE curtime timestamp := now(); BEGIN UPDATE users SET last_modified = curtime, comment = comment WHERE users.id = id; END; $$ LANGUAGE plpgsql;
I think you’re looking for SELECT select_expressions INTO: select test_table.name into name from test_table where id = x; That will pull the name from test_table where id is your function’s argument and leave it in the name variable. Don’t leave out the table name prefix on test_table.name or you’ll get complaints about an ambiguous reference.
FrustratedWithFormsDesigner is correct, PL/pgSQL can do this. Here’s the script: CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$ DECLARE statements CURSOR FOR SELECT tablename FROM pg_tables WHERE tableowner = username AND schemaname=”public”; BEGIN FOR stmt IN statements LOOP EXECUTE ‘TRUNCATE TABLE ‘ || quote_ident(stmt.tablename) || ‘ CASCADE;’; END LOOP; END; $$ LANGUAGE … Read more