Generate HTML from PostgreSQL function

Question

Hi, 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;

asked Nov 24 at 9:21

Adomas
705

83% accept rate

	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 at 13:32
	Do you know psql has the -H html switch? `psql -H -c'select * from table'.` – nate c Nov 24 at 19:13

Do NOT use the IMMUTABLE qualifier with that function! PostgreSQL could make unsafe optimizations. Read: postgresql.org/docs/9.0/interactive/xfunc-volatility.html
Do you know psql has the -H html switch? psql -H -c'select * from table'.

Edmund · Answer 1 · 2010-11-24 09:59:05Z

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...

this gives a lot of syntax errors and I perhaps as a beginer can't handle them well

dimitris mistriotis · Answer 2 · 2010-11-24 09:34:17Z

up vote 2 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.

answered Nov 24 at 9:34

dimitris mistriotis
45829

	I would, definitely would, but the task is to do it exactly on plpgsql and I can't change that :( – Adomas Nov 24 at 9:40
	ok 1) This script seems to generate only rows (I see a tr and not a td) – dimitris mistriotis Nov 24 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 at 9:47
	archives.postgresql.org/pgsql-sql/1999-12/msg00154.php – dimitris mistriotis Nov 24 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... – dimitris mistriotis Nov 24 at 9:55

Generate HTML from PostgreSQL function

2 Answers

Your Answer

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

Hello World!

Generate HTML from PostgreSQL function

2 Answers

Your Answer

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

Hello World!

Related