By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601556 fiddles created (47966 in the last week).
CREATE TABLE "MyTableName"( id int, first varchar(32));
INSERT INTO "MyTableName" VALUES (1, 'foo');
✓
1 rows affected
hidden batch(es)
SELECT format('SELECT %s FROM "MyTableName"'
, string_agg(CASE WHEN udt_name in ('varchar', 'text')
THEN format('left(%1$I, 65535) AS %1$I', column_name)
ELSE quote_ident(column_name) END
, ', ' ORDER BY ordinal_position))
FROM information_schema.columns c
JOIN parse_ident('"MyTableName"') t ON t[1] = c.table_name;
-- can't use full form with secrect schema name
-- JOIN parse_ident('"public"."MyTableName"') t ON t[1] = c.table_schema
-- AND t[2] = c.table_name
format
SELECT id, left(first, 65535) AS first FROM "MyTableName"
…
hidden batch(es)
-- what I would do:
SELECT format('SELECT %s FROM %s'
, string_agg(CASE WHEN atttypid = ANY ('{text, bpchar, varchar}'::regtype[])
THEN concat('left(', col, ', 65535) AS ', col)
ELSE col END, ', ')
, attrelid)
FROM (
SELECT attrelid::regclass, atttypid, quote_ident(attname) AS col
FROM pg_catalog.pg_attribute
WHERE attrelid = 'public."MyTableName"'::regclass -- provide once, optionally schema-qualified
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum
) sub
GROUP BY attrelid;
format
SELECT id, left(first, 65535) AS first FROM "MyTableName"
…
hidden batch(es)
SELECT id, left(first, 65535) AS first FROM "MyTableName";
