SQL state: 42883 in PostgreSQL 9.3

Question

I have the following table called as test_type which contains two columns namely cola and colb.

Table: test_type

create table test_type
(
    cola int,
    colb varchar(50)
);

Now I want to create a type with same columns.

Type: type1

create type type1 as
(
cola int,
colb varchar(50)
);

Here I have created function in which I am passing type name type1 to insert the data to the table test_type.

--Creating Function

create or replace function fun_test ( p_Type type1 )
returns void
as
$$
begin
    insert into test_type(cola,colb) 
    select cola,colb from p_type
    EXCEPT
    select cola,colb from test_type;
end
$$
language plpgsql;

---Calling Function

SELECT fun_test(1,'Xyz');

Error Details:

ERROR: function fun_test(integer, unknown) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 8

`select cola,colb from p_type` will not work. You don't have a table with that name - at least not in the example you have shown. If your intention is to pass a table name as a parameter, then this will not work. You need dynamic SQL for that. See e.g. here: http://stackoverflow.com/q/22753868/330315 and here: http://stackoverflow.com/q/10705616/330315 and here: http://stackoverflow.com/q/33701082/330315 — , Dec 11 '15 at 06:58
@a_horse_with_no_name, Yeah! I got it. Thank you so much for providing useful links. — MAK, Dec 11 '15 at 07:06

Tom-db · Accepted Answer · 2015-12-11T08:10:23.507

3

You need to "pack" the arguments together: (1,'xs'), so that postgres recognise them as single argument of type type1:

SELECT fun_test((1,'xs'));

For a better readability you can cast the argument to type1 (not really necessary):

SELECT fun_test((1,'xs')::type1);

If the purpose of the function is to to insert the values only if they are not already contained in the table, you could change your code so:

create or replace function fun_test ( p_Type type1 )
  returns void AS $$
BEGIN
    INSERT INTO test_type(cola,colb)
    SELECT p_Type.cola,p_Type.colb
    EXCEPT
    SELECT cola,colb FROM test_type;
END; 
$$ language plpgsql;

But this syntax is my opinion not good readable. This statement looks better:

...
BEGIN
  PERFORM 0 FROM test_type WHERE (cola, colb) = p_Type;
  IF NOT FOUND THEN
    INSERT INTO test_type(cola,colb) VALUES (p_Type.cola,p_Type.colb);
  END IF;
END;
...

edited Dec 11 '15 at 08:10

answered Dec 11 '15 at 06:26

Tom-db

6,528
3
30
44

Still getting an error but this time different relation `p_Type` does not exists. – MAK Dec 11 '15 at 06:51
This only means that the table p_type does not exist. Create it and it will exist. – Tom-db Dec 11 '15 at 06:56
Sorry, I misunderstood your question. Check out the answer again, I added some code. I hope now I understood correctly. – Tom-db Dec 11 '15 at 07:42
Yes! This is what my question is. But I still accept it because this --> `SELECT fun_test((1,'xs'));` solve my error and I did it with different way by casting `p_Type` to `type1`. Anyway its really a good idea what you have written in last statement. – MAK Dec 11 '15 at 11:11

SQL state: 42883 in PostgreSQL 9.3

1 Answers1