Why should you care?

PostgresVerse admin tool sorts any whitelisted column without writing 50 static queries — dynamic ORDER BY with validation.

See it live — copy this example

Run in pgAdmin or psql.

CREATE OR REPLACE FUNCTION list_products(sort_col text, lim int)
RETURNS SETOF products
LANGUAGE plpgsql
AS $$
BEGIN
  IF sort_col NOT IN ('price','name','rating') THEN
    RAISE EXCEPTION 'invalid sort column';
  END IF;
  RETURN QUERY EXECUTE format(
    'SELECT * FROM products ORDER BY %I NULLS LAST LIMIT %s',
    sort_col, lim
  );
END;
$$;

SELECT * FROM list_products('price', 5);

What happened?

• format %I quotes identifier safely.
• Whitelist blocks SQL injection via sort_col.
• EXECUTE runs generated SELECT returning product rows.

Practice next

1. Create function and call with price and name.
2. Try invalid sort_col and read exception.
3. Log generated SQL with RAISE NOTICE in dev.
4. Add USING clause with bound parameter: EXECUTE ... USING cust_id.
5. Build pivot report over whitelisted metric columns.

Remember

EXECUTE runs string SQL inside plpgsql. format %I for columns/tables; %L for literals. Whitelist dynamic parts.

PostgresVerse admin grid

Internal ops UI passes sort column; function validates and runs one query plan family.

Outcome: Feature ships without ORM supporting every sort permutation.