PostgreSQL eval() functie
Nu moet ik mijn SQL-genererende database class aan het aanpassen. Het haalt van tabellen kolominformatie op. Eerder deed ik dat met driverinformatie via mysqli() en 'SHOW FULL COLUMNS', maar dat werkt in PG anders.
Ik dacht eerst aan iets als:
Code (php)
1
SELECT * FROM "tabelnaam" LIMIT 0 OFFSET 0
om vervolgens met pg_field_name() en pg_field_type() kolominformatie te achterhalen. Vind ik later een nog makkelijker functie: http://php.net/manual/en/function.pg-meta-data.php
Maar nu wil ik meer metadata selecteren, zoals de default waarde, voor bv. het editen in mijn appje. Als ik die opvraag met:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
"attname"
"pg_catalog".format_type("a"."atttypid", "a"."atttypmod") AS "atttyp"
"attnotnull"
( SELECT "pg_catalog".pg_get_expr("b"."adbin", "b"."adrelid")
FROM "pg_catalog"."pg_attrdef" AS "b"
WHERE "b"."adrelid" = "a"."attrelid"
AND "b"."adnum" = "a"."attnum"
AND "a"."atthasdef"
) AS "attdefault"
FROM
"pg_catalog"."pg_attribute" AS "a"
WHERE
"a"."attrelid" = '"schemanaam"."tabelnaam"'::regclass
AND "a"."attnum" > 0 -- no system columns
AND NOT "a"."attisdropped" -- no dropped columns
ORDER BY
"a"."attnum"
;
"attname"
"pg_catalog".format_type("a"."atttypid", "a"."atttypmod") AS "atttyp"
"attnotnull"
( SELECT "pg_catalog".pg_get_expr("b"."adbin", "b"."adrelid")
FROM "pg_catalog"."pg_attrdef" AS "b"
WHERE "b"."adrelid" = "a"."attrelid"
AND "b"."adnum" = "a"."attnum"
AND "a"."atthasdef"
) AS "attdefault"
FROM
"pg_catalog"."pg_attribute" AS "a"
WHERE
"a"."attrelid" = '"schemanaam"."tabelnaam"'::regclass
AND "a"."attnum" > 0 -- no system columns
AND NOT "a"."attisdropped" -- no dropped columns
ORDER BY
"a"."attnum"
;
Krijg ik de default waarde niet, maar wel de expressie die leidt tot de default waarde. Nu wil ik de default waarden het liefst in 1 query selecteren met iets als een eval() functie. Die bestaat niet in PG maar is makkelijk zelf te maken http://stackoverflow.com/questions/7433201/are-there-any-way-to-execute-a-query-inside-the-string-value-like-eval-in-post
Maar weet iemand misschien een makkelijker manier? Wat ik tot nu toe heb lijkt me een beetje gepunnik in de marge.
Stel bijvoorbeeld dat de expressie een nextval is voor een van je sequences, wat uiteraard het geval is voor bijvoorbeeld je id's. Dan zal de expressie worden uitgevoerd en de sequence worden verhoogd. Dit wordt ook niet teruggedraaid bij rollback. En dit is nog maar een van de pitfalls.
Over de vraag zelf: dat kun je doen met een stored-function:http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Maaruhm, is er dat jullie weten geen andere manier om PG de defaultwaarde te laten geven?
Voor de volledigheid, de code die ik had en die we dus vooral niet moeten gebruiken:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
CREATE OR REPLACE FUNCTION schemanaam.eval(expression text) RETURNS text AS $$
declare
result text;
begin
execute 'SELECT ' || expression into result;
return result;
end;
$$ LANGUAGE plpgsql;
declare
result text;
begin
execute 'SELECT ' || expression into result;
return result;
end;
$$ LANGUAGE plpgsql;
in combinatie met:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
"attname"
"pg_catalog".format_type("a"."atttypid", "a"."atttypmod") AS "atttype"
"attnotnull"
"pg_catalog".pg_get_expr("b"."adbin", "b"."adrelid") AS "atdefault",
CASE WHEN "pg_catalog".pg_get_expr("b"."adbin", "b"."adrelid") IS NOT NULL THEN
schemanaam.eval("pg_catalog".pg_get_expr("b"."adbin", "b"."adrelid"))
END AS "atdefval"
FROM
"pg_catalog"."pg_attribute" AS "a"
LEFT JOIN "pg_catalog"."pg_attrdef" AS "b" ON (
"b"."adrelid" = "a"."attrelid"
AND "b"."adnum" = "a"."attnum"
AND "a"."atthasdef"
)
WHERE
"a"."attrelid" = '"schemanaam"."tabelnaam"'::regclass
AND "a"."attnum" > 0 -- no system columns
AND NOT "a"."attisdropped" -- no dropped columns
ORDER BY
"a"."attnum"
;
"attname"
"pg_catalog".format_type("a"."atttypid", "a"."atttypmod") AS "atttype"
"attnotnull"
"pg_catalog".pg_get_expr("b"."adbin", "b"."adrelid") AS "atdefault",
CASE WHEN "pg_catalog".pg_get_expr("b"."adbin", "b"."adrelid") IS NOT NULL THEN
schemanaam.eval("pg_catalog".pg_get_expr("b"."adbin", "b"."adrelid"))
END AS "atdefval"
FROM
"pg_catalog"."pg_attribute" AS "a"
LEFT JOIN "pg_catalog"."pg_attrdef" AS "b" ON (
"b"."adrelid" = "a"."attrelid"
AND "b"."adnum" = "a"."attnum"
AND "a"."atthasdef"
)
WHERE
"a"."attrelid" = '"schemanaam"."tabelnaam"'::regclass
AND "a"."attnum" > 0 -- no system columns
AND NOT "a"."attisdropped" -- no dropped columns
ORDER BY
"a"."attnum"
;
Met de hamvraag; hoe kan het beter in PG?
SELECT column_name, column_default
FROM information_schema.columns
WHERE (table_schema, table_name) = ('public', 'mytable')
ORDER BY ordinal_position;
Zie ook:
http://stackoverflow.com/questions/8146448/get-the-default-values-of-table-columns-in-postgres
Ook daar zul je nog wat parsing moeten doen voor serials maar dit vraag in ieder geval alleen op, het voert niets uit.
Ik neem tenminste aan dat je alleen wilt weten of er een default is en of dat een vaste waarde is?
Toevoeging op 09/11/2015 20:56:03:
Ben niet de enige die dat wil: http://stackoverflow.com/questions/24131640/how-to-retrieve-the-actual-default-value-for-a-column-before-insertion