CODEWITHSUNDEEP
postgresqlpostgresql-9.5
barteloma
barteloma

Reputation: 6845

How can I get type of column in postgresql trigger function

I want to get type of column from postgresql trigger function.

This query returns type of items:

Select pg_typeof(shape) from sde.my_points;

And this query returns type of geometry:

Select  GeometryType(shape) from sde.my_points;

I want to use this parts in postgresql trigger function:

 CREATE FUNCTION point_xy() RETURNS trigger AS $point_xy$
        BEGIN
                IF (SELECT pg_typeof(NEW.shape)) = ('geometry')
                   AND  GeometryType(NEW.shape) = ('POINT')
                THEN
                        NEW.x = st_x(NEW.shape);
                        NEW.y = st_y(NEW.shape);
                END IF;
            END IF;

            RETURN NEW;
        END
        $point_xy$ LANGUAGE plpgsql;

This function gives error, in this line (SELECT pg_typeof(NEW.shape)).

ERROR: invalid input syntax for type oid: "geometry"

QUERY: SELECT (SELECT pg_typeof(NEW.shape)) = ('geometry') AND GeometryType(NEW.shape) = ('POINT')

CONTEXT: PL/pgSQL function point_xy() line 7

How can I use in function?

Upvotes: 0

Views: 1099

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21326

pg_typeof() returns a regtype value, but the associated = operator can compare any kind of OIDs, so the value on the other side could be a type, a table, a function... The string 'geometry' can't be automatically interpreted as a type name, so you'll need to cast it yourself. Try:

pg_typeof(NEW.shape) = 'geometry'::regtype

Upvotes: 2

Related Questions