I'm getting an error when a trigger is run and trying to run the following function
CREATE OR REPLACE FUNCTION insert_new_sessions()
RETURNS trigger AS
$$
DECLARE
BEGIN
SELECT dblink_exec('dbname=billing port=5432','INSERT INTO md.radacct_test(radacctid,acctsessionid,acctuniqueid,username) select New.radacctid,New.acctsessionid,New.acctuniqueid,New.username' );
RETURN NULL;
EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'insert_new_sessions SQL ERROR: %', SQLERRM;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
The error:
ERROR insert_new_sessions SQL ERROR: missing FROM-clause entry for table "new"
Because you have NEW inside the string, thus interpreted as a string? Try concatenating the NEW.xxx values with the rest of the string.
Eg.
INSERT INTO md.radacct_test(...) SELECT ' || NEW.radacctid ||', ' || NEW.acctsessionid || ', ' || NEW.acctuniqueid || ', ' || quote_nullable(NEW.username));