type "xxxxxxx" does not exist

From: Micky Hulse <mickyhulse(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: type "xxxxxxx" does not exist
Date: 2017-05-19 20:06:43
Message-ID: CAKzdcNnYDS9yBSk_SAHkR9em=9g38uTd1W0-54xmq7vx_rUtww@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I hope this is the right list for me to ask questions about psql.
Please let me know if I am in the wrong place. :)

I am far from an advanced user of PostgreSQL, so please bear with me ...

I am working with an inherited database/codebase. I am trying to call
this function via psql:

# SELECT * FROM functionName('xxxxxxx', 'xxxxxxx', 'xxxxxxx');

What I get back is this:

ERROR: type "xxx_xxx_xxxxx" does not exist
LINE 1: DECLARE results xxx_xxx_xxxxx;
^
QUERY: DECLARE results xxx_xxx_xxxxx;
.....
.....

When listing the functions, I see that functionName() does exist in
the database.

The type also exists (I think):

# select exists (select 1 from pg_type where typname = 'xxx_xxx_xxxxx');
exists
--------
t
(1 row)

Note that the role that owns the 'type' is not the same user that is
calling the "functionName()" from the psql prompt. When I try to
switch roles, using:

sudo -i -u username
psql -U otherusername -d database

… I get:

psql: FATAL: Peer authentication failed for user "otherusername"

Do I need to create a Linux user to login as "otherusername" so I can
test calling the functionName() with xxx_xxx_xxxxx type?

Lastly, the type was declared in the SQL dump like this:

CREATE TYPE xxx_xxx_xxxxx AS (
....

);
ALTER TYPE xxx_xxx_xxxxx OWNER TO otherusername;

I know that's a lot of info ... More than anything, I'm just wondering
if someone can give me tips on where to focus my attention in terms of
trouble shooting?

Thanks so much!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2017-05-19 20:20:06 Re: type "xxxxxxx" does not exist
Previous Message Eric Hill 2017-05-19 19:04:16 Re: storing large files in database - performance