1

I am running PostgreSQL 9.3.5 and am seeing a difference between creating a function in PSQL vs. the pgAdmin SQL window. I have a function that I want to create (and deploy) via PSQL.

The code is:

CREATE OR REPLACE FUNCTION sync_division_owner_customer_owner_number()
  RETURNS trigger AS
$BODY$
DECLARE 
    row_count           integer                                     := 0;
    cust_uid            customer.customer_uid%TYPE                  := 0;
    cust_date_last_paid customer_owner_number.date_last_paid%TYPE;
BEGIN
    SELECT COALESCE(customer_uid,0) INTO cust_uid FROM division_order D WHERE D.division_order_uid = NEW.division_order_uid;
    IF cust_uid = 0 THEN
        RAISE EXCEPTION 'customer_uid found for function sync_division_owner_customer_owner_number';
    END IF;
    SELECT count(*) INTO row_count FROM customer_owner_number A WHERE A.customer_uid = cust_uid AND A.owner_number = NEW.owner_number;
    CASE row_count
        WHEN 0 THEN
            INSERT INTO customer_owner_number(owner_number, date_first_paid, date_last_paid, customer_uid, deadfiled) 
                VALUES(NEW.owner_number, NEW.date_last_paid, NEW.date_last_paid, cust_uid, TRUE);
        WHEN 1 THEN
           SELECT A.date_last_paid INTO cust_date_last_paid FROM customer_owner_number A WHERE A.customer_uid = cust_uid AND A.owner_number = NEW.owner_number;
           IF cust_date_last_paid < NEW.date_last_paid THEN
                UPDATE customer_owner_number A SET A.date_last_paid = cust_date_last_paid
                    WHERE A.customer_uid = cust_uid AND A.owner_number = NEW.owner_number;
           END IF;
    END CASE;
    RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

When I run this file using: psql -U (user) -d test -f "C:\test\function.sql" I get an error:

ERROR:  invalid type name "customer.customer_uid%TYPE"

Yet, if I run this same exact code in the SQL window, I have no problems at all and it creates the function like it should. I need to use the %TYPE variable, so it is not an option to change it.

I would appreciate anyone telling me what else I could to do to fix this for PSQL.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Greg Simpson
  • 11
  • 3
  • I think there must be a space between `psql -U (user) -d test -f "C:\test\function.sql` try this if it work fine then i will explain why it happens. – smn_onrocks Jan 02 '15 at 06:27
  • 4
    Are you *absolutely certain* you're connecting to the same database on the same PostgreSQL instance in both cases? – Craig Ringer Jan 02 '15 at 07:06
  • 2
    Are you certain that you're connected as the same user in both cases as well. – Joe Love Jan 02 '15 at 08:08
  • 1
    psql is client just like pgAdmin. If your code works on a database using pgAdmin, it also works for the same database (!) using a different client (like pgsql). – Frank Heikens Jan 02 '15 at 08:47
  • @ snm_onrocks This was a typo on entry. – Greg Simpson Jan 02 '15 at 17:17
  • @Craig Ringer - I am absolutely certain. I can give you the entire function if you wish and you can see for yourself when doing it from PSQL you will get an error and when running from the SQL window you will succeed. – Greg Simpson Jan 02 '15 at 17:26
  • Can you confirm that `select count(customer_uid) from customer';` works in psql? any result other than an error is acceptable. – Jasen Jan 03 '15 at 21:54
  • @Jasen - I have confirmed that "select count(customer_uid) from customer;" works and gives me a count of the rows. – Greg Simpson Jan 04 '15 at 01:36
  • ok, thanks. I can't think of what the problem could be. sorry. – Jasen Jan 04 '15 at 01:44
  • It would have been helpful to include information on the table `customer`: table definition and the schema in which it was created. – Erwin Brandstetter Jan 04 '15 at 03:16

1 Answers1

0

If customer.customer_uid is valid for a %TYPE declaration in one client, but not in another client, it must be a question of visibility.

Either your search_path setting is different or temporary tables play a role (which are only visible inside the same session). You can defend against all possible causes in a number of ways. Here are two:

Assuming the table customer lives in the schema my_schema:

1. Schema-qualify the reference:

cust_uid  my_schema.customer.customer_uid%TYPE := 0;

2. SET a search_path for the scope of the function:

CREATE OR REPLACE FUNCTION sync_division_owner_customer_owner_number()
  RETURNS void AS
$func$

 ...
$func$  LANGUAGE plpgsql
        SET search_path = my_schema,public,pg_temp;

Note that your trigger function still won't work, because there is at least one more syntax error:

UPDATE customer_owner_number A SET A.date_last_paid = cust_date_last_paid

Per documentation:

Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

I didn't audit further.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228