从PostgreSQL函数生成HTML [英] Generate HTML from PostgreSQL function

问题描述

有人可以帮我吗?我有一个编写函数的任务，该函数将从PostgreSQL(plpgsql语言)中的给定表名生成HTML表. 我已经写了这个，但是远远超出了我的需要.它将为我要提供的列生成一个表(目前只有一个)，但是我只需要给表起一个名字即可.

Can anyone help me with this? I have a task to write 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 a table for columns I would give (at the moment just one), but I need to just give the table a name.

创建或替换功能genhtml2(表名文本，列名文本)
以AS形式返回文字 $ BODY $ 宣布 结果文本:=''; searchsql文字:=''; var_match text:='';开始 searchsql:='选择'||列名|| ' 从'||表名|| '';

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

返回结果；结尾; $ BODY $语言"plpgsql" IMMUTABLE；

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

推荐答案

您可以先在calalogs中搜索表中的列，然后使用它们生成查询并设置表头.

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

这篇关于从PostgreSQL函数生成HTML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！