html 支持3pg格式吗,从PostgreSQL函数生成HTML

撇开上面的其他答案,我修改了这个,因为我在上面列出的答案中发现了几个问题,其中包括:

连接不正确;别名无效(即n)

函数无法处理空值

函数应生成具有定义的HTML文档类型的整个HTML文档

注意:虽然必须在postgres中生成HTML文档并不理想,但有些情况下可能需要这样做。我发现自己在里面。除了上面列出的问题外,我还包括用于处理表上格式和css的引导。我希望这对其他人有帮助。

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 := '';

varmatch TEXT := '';

col RECORD;

html_doctype TEXT := '' || E'\n';

html_meta TEXT := '' || E'\n\t' || '' || E'\n';

html_link TEXT := '' || E'\n';

html_bscript TEXT := '' || E'\n';

html_jscript TEXT := '' || E'\n';

html_head TEXT := '' || E'\n' || '

' || E'\n\t' || html_meta || E'\t' || hmtml_link || E'\t' || html_jscript || E'\t' || html_bscript || '' || E'\n';

html_body TEXT := '

';

header TEXT;

BEGIN

header := E'\t'|| '

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

JOIN pg_namespace AS n ON n.oid = c.relnamespace

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

' || col || '' || E'\n';

searchsql := searchsql || $QUERY$ || E'\n\n\t' || '

' || $QUERY$ || 'coalesce(' || col || ', ''N/A'')' || $QUERY$ || '' $QUERY$;

END LOOP;

header := header || E'\t' || '

' || E'\n';

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

result := html_doctype || html_head || html_body || E'\n\t' || '

result := result || header;

FOR varmatch IN EXECUTE (searchsql) LOOP

IF result > '' THEN

result := result || E'\t' || '

' || varmatch || E'\n\t' || || E'\n';

END IF;

END LOOP;

result := result || E'\t' ||

|| E'\n' || ' || E'\n' || '';

RETURN result;

END;

$body$

LANGUAGE 'plpgsql' VOLATILE;