CODEWITHSUNDEEP
sqlpostgresqlplpgsqldynamic-sql
PathmanKIP
PathmanKIP

Reputation: 705

Get column names for a given table name from a function

I want to call table name manually input type then result should be table's details, I tried those function

  1. 1st function is working.
  2. 2nd function is not working.

1)

DECLARE      
All_columns varchar;        
Tab_name ALIAS FOR $1 ;       
BEGIN       
FOR All_columns IN SELECT column_name       
FROM information_schema.columns      
WHERE table_name=Tab_name     
loop      
raise notice 'Columns:%',All_columns;     
end loop;     
return All_columns;    
END;    

select test_levelfunction1('country_table');

It shows all columns of country table

2) 

DECLARE     
All_columns varchar    ;          
Tab_name ALIAS FOR $1  ;      
BEGIN          
FOR All_columns IN SELECT Tab_name.*     
FROM Tab_name     
loop     
raise notice 'Columns:%',All_columns;     
end loop;     
return All_columns;     
END;

The call select test_levelfunction1('country_table'); results in an error.
I need all the details from country_table.
How can I fix this function?

Upvotes: 1

Views: 163

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

You can largely simplify this task. This SQL function does the job:

CREATE OR REPLACE FUNCTION f_columns_of_tbl(_tbl regclass)
  RETURNS SETOF text AS
$func$
SELECT quote_ident(attname) AS col
FROM   pg_attribute
WHERE  attrelid = $1              -- valid, visible table name 
AND    attnum >= 1                -- exclude tableoid & friends
AND    NOT attisdropped           -- exclude dropped columns
ORDER  BY attnum
$func$ LANGUAGE sql;

Call:

SELECT f_columns_of_tbl('myschema.mytable');  -- optionally schema-qualified name

For more details, links and a plpgsql version consider the related answer to your last question:
PLpgSQL function to find columns with only NULL values in a given table

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78423

Neither function works, insofar as I read them. Or then you expect the first to return your input instead of column names.

You probably want to be using dynamic sql in both functions, e.g.:

EXECUTE $x$SELECT * FROM $x$ || Tab_name::regclass

http://www.postgresql.org/docs/current/static/plpgsql-statements.html

Upvotes: 1

Related Questions