Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

postgresql - Creating a trigger for child table insertion returns confusing error

I am trying to write a trigger function that will input values into separate child tables, however I am getting an error I have not seen before.

Here is an example set up:

-- create initial table
CREATE TABLE public.testlog(
    id serial not null,
    col1 integer,
    col2 integer,
    col3 integer,
    name text
);

-- create child table
CREATE TABLE public.testlog_a (primary key(id)) INHERITS(public.testlog);

-- make trigger function for insert
CREATE OR REPLACE FUNCTION public.test_log() RETURNS trigger AS
$$
DECLARE
    qry text;
BEGIN
    qry := 'INSERT INTO public.testlog_' || NEW.name || ' SELECT ($1).*';

    EXECUTE qry USING NEW.*;

    RETURN OLD;
END
$$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

-- add function to table
CREATE TRIGGER test_log_sorter BEFORE INSERT
ON public.testlog FOR EACH ROW
EXECUTE PROCEDURE public.test_log();

and the query:

INSERT INTO public.testlog (col1, col2, col3, name) values (1, 2, 3, 'a');

error message:

[Err] ERROR:  query "SELECT NEW.*" returned 5 columns
CONTEXT:  PL/pgSQL function test_log() line 7 at EXECUTE statement

5 columns is exactly what I am looking for it to return, so clearly there is something I am not understanding but the error message seems to make no sense.

Can anybody explain why I am getting this?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Your solution fixes the passing of the row-typed NEW variable. However, you have a sneaky SQL-injection hole in your code, that's particularly dangerous in a SECURITY DEFINER function. User input must never be converted to SQL code unescaped.

Sanitize like this:

CREATE OR REPLACE FUNCTION trg_test_log()
  RETURNS trigger AS
$$
BEGIN
    EXECUTE 'INSERT INTO public.' || quote_ident('testlog_' || NEW.name)
         || ' SELECT ($1).*'
    USING NEW;

    RETURN NULL;
END
$$
LANGUAGE plpgsql SECURITY DEFINER;

Also:

  • OLD is not defined in an INSERT trigger.
  • You don't need a variable. Assignments are comparatively expensive in plpgsql.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...