Received: from virtuel.qc.ca (virtuel.qc.ca [207.96.141.2]) by hub.org (8.9.3/8.9.3) with SMTP id QAA43592 for ; Tue, 29 Jun 1999 16:00:52 -0400 (EDT) (envelope-from hostmaster@virtuel.qc.ca) Received: from maximum [207.96.141.5] by virtuel.qc.ca (SMTPD32-4.07) id A5D35CA0106; Tue, 29 Jun 1999 16:00:19 EDT Message-ID: <002801bec26a$19c73480$058d60cf@virtuel.qc.ca> From: "Hostmaster - Internet au Virtuel Inc." To: "pgsql-sql list" Subject: User defined functions through ODBC Date: Tue, 29 Jun 1999 16:00:56 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2014.211 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211 Hi I'm running PostgreSQL v6.4.2 on Red Hat Linux 6.0. I use it on a Windows NT server 4.0 with service pack 5 through ODBC calls; the ODBC driver is the one from Insight Distributions System, v6.40.00.06. The IP address of the NT machine is specified as "trust" in pg_hba.conf. I've been playing a bit with PL/pgSQL ... I find it quite usefull. But I have one problem: The functions I create seem to be accessible only from within the psql utility. When I try to run a query from the network, I get an ODBC error stating that the function doesn't exist. Queries not involving user-defined functions run fine. Here is one of these functions: create function plan_text(int2) returns text as ' begin if $1 is null then return ''10 hrs''; end if; if $1 <= 10 then return ''10 hrs''; end if; if $1 <= 30 then return ''30 hrs''; end if; if $1 <= 60 then return ''60 hrs''; end if; return ''Illimité''; end; ' language 'plpgsql'; So this query works from within psql: SELECT hist_id, client_id, month, plan_text(plan) FROM history WHERE client_id = 3; But not through a network ODBC call. What am I missing? I created the user "sql", and everything in this database is owned and operated with this uid. I tried creating the function as the "postgres" superuser; I tried to authenticate differently, but I get nothing. If I use pgAdmin 6.4.3 beta, and have it list the functions, "plan_text" appears in the list, but the query fails. Anyone has an idea? Thanks, Nicolas Cadou