current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.6 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 2 down vote favorite
1

can anyone help me with this. I have a task to rite a function, which would generate html tables from given table name in postgreSQL(plpgsql language). I have written this, but it's far from what I need. It would generate table for columns I would give(now just one), but I need to give just table name.

CREATE OR REPLACE FUNCTION genhtml2(tablename text, columnname text)
RETURNS text AS $BODY$ DECLARE result text := ''; searchsql text := ''; var_match text := ''; BEGIN searchsql := 'SELECT ' || columnname || ' FROM ' || tablename || '';

result := '<table>';
FOR var_match IN EXECUTE(searchsql) LOOP
    IF result > '' THEN
        result := result || '<tr>' || var_match || '</tr>';
    END IF;
END LOOP;
result :=  result || '</table>';

RETURN result; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE;

share|improve this question
    
Do NOT use the IMMUTABLE qualifier with that function! PostgreSQL could make unsafe optimizations. Read: postgresql.org/docs/9.0/interactive/xfunc-volatility.html – intgr Nov 24 '10 at 13:32
    
Do you know psql has the -H html switch? psql -H -c'select * from table'. – nate c Nov 24 '10 at 19:13
up vote 1 down vote accepted

You could search the calalogs for the columns in the table first, then use them to generate the query and to set the table header.

colsql := $QUERY$SELECT attname
                 FROM pg_attribute AS a JOIN pg_class AS c ON a.attrelid = c.oid
                 WHERE c.relname = '$QUERY$
          || tablename || $QUERY$' AND attnum > 0;$QUERY$;

header := '';
searchsql := $QUERY$SELECT ''$QUERY$;
FOR col IN EXECUTE colsql LOOP
    header := header || '<th>' || col || '</th>';
    searchsql := searchsql || $QUERY$||'<td>'||$QUERY$ || col;
END LOOP;

searchsql := searchsql || ' FROM ' || tablename;

-- rest of your function here

Obviously this gets messy and brittle fast...

share|improve this answer
    
this gives a lot of syntax errors and I perhaps as a beginer can't handle them well – Adomas Nov 24 '10 at 10:25
    
nice, I worked it out, thanks – Adomas Nov 24 '10 at 10:39
up vote 3 down vote

I am quite confident that you should not do this because it is a potential maintenance nightmare. The best thing to do is return the row results to any application or another layer and work from there towards html.

share|improve this answer
    
I would, definitely would, but the task is to do it exactly on plpgsql and I can't change that :( – Adomas Nov 24 '10 at 9:40
    
ok 1) This script seems to generate only rows (I see a tr and not a td) – Dimitrios Mistriotis Nov 24 '10 at 9:44
    
well, while I am giving to function just one column here, I don't need td. But the idea is that I should not give any column names and function must find them, if I do so, I'll bring td's in – Adomas Nov 24 '10 at 9:47
    
archives.postgresql.org/pgsql-sql/1999-12/msg00154.php – Dimitrios Mistriotis Nov 24 '10 at 9:54
    
You want to retrieve the meta information of the table and then use it... unfortunately I do not have a postgre instance to try by myself... – Dimitrios Mistriotis Nov 24 '10 at 9:55
up vote 1 down vote

Here's a modified version that works with multiple columns using a text[] array for the column names. It also prints new lines and tabs to format the output.

CREATE OR REPLACE FUNCTION genhtml(text, text, text, text[])
  RETURNS text AS $BODY$ 

DECLARE 
  schemaname ALIAS FOR $1;
  tablename ALIAS FOR $2;
  tabletype ALIAS FOR $3;
  columnnames ALIAS FOR $4;
  result TEXT := ''; 
  searchsql TEXT := ''; 
  var_match TEXT := ''; 
  col RECORD;
  header TEXT;

BEGIN

  header := E'\t' || '<tr>' || E'\n';
  searchsql := $QUERY$SELECT ''$QUERY$;
  FOR col IN SELECT attname 
    FROM pg_attribute AS a 
    JOIN pg_class AS c ON a.attrelid = c.oid 
    WHERE c.relname = tablename
        AND n.nspname = schemaname
        AND c.relkind = tabletype
        AND attnum > 0 
        AND attname = ANY(columnnames)
  LOOP
    header := header || E'\t\t' || '<th>' || col || '</th>' || E'\n';
    searchsql := searchsql || $QUERY$ || E'\n\t\t' || '<td>' || $QUERY$ || col ||     $QUERY$ || '</td>' $QUERY$;
  END LOOP;
  header := header || E'\t' || '</tr>' || E'\n';

  searchsql := searchsql || ' FROM ' || schemaname || '.' || tablename;

  result := '<table>' || E'\n';
  result := result || header;
  FOR var_match IN EXECUTE(searchsql) LOOP
    IF result > '' THEN
      result := result || E'\t' || '<tr>' || var_match || E'\n\t' || '</tr>' || E'\n';
    END IF;
  END LOOP;
  result :=  result || '</table>' || E'\n';

  RETURN result; 

END; 
$BODY$ 
  LANGUAGE 'plpgsql' VOLATILE;

Call the function with something like:

SELECT genhtml('public', 'tablenamehere', 'r', ARRAY['col1', 'col2', 'col3']);

The 'r' is for normal tables. If you are using a VIEW instead, change it to 'v'.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.