pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Davis <mdavis@sevainc.com>
To: 'Ken Corey' <ken@kencorey.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: RE: Hrm...why is this wrong?
Date: Mon, 5 Feb 2001 08:24:55 -0700
Message-ID: <01C08F4D.22CA2220.mdavis@sevainc.com> (raw)

When declaring parameters try using varchar rather than varchar(255).  I have used text successfully in the past.

-----Original Message-----
From:	Ken Corey [SMTP:ken@kencorey.com]
Sent:	Sunday, February 04, 2001 6:38 AM
To:	pgsql-sql@postgresql.org
Subject:	Hrm...why is this wrong?

In trying to use a plpgsql stored proc, I'm getting an error I don't 
understand.

When the select at the bottom of this email is executed, I'm getting the 
message:

ERROR:  parser: parse error at or near "$1"

Any ideas? <Gads I hope it's not something amazingly simple, but I'm sure it 
is...;^>

-- 
Ken Corey, CTO    Atomic Interactive, Ltd.

select 'drop FUNCTION IU_EMPLOYEE(varchar(255), ...);' as Progress;
drop FUNCTION IU_EMPLOYEE( varchar(255), varchar(255),
  varchar(255), varchar(255), varchar(255), varchar(255),
  varchar(255), varchar(255), varchar(255), varchar(255),
  varchar(255));

select 'create FUNCTION IU_EMPLOYEE(varchar(255), ...)' as Progress;
create FUNCTION IU_EMPLOYEE( varchar(255), varchar(255),
  varchar(255), varchar(255), varchar(255), varchar(255),
  varchar(255), varchar(255), varchar(255), varchar(255),
  varchar(255))
RETURNS INT4
AS '
  DECLARE
    user_name_in alias for $1;
    passwd_in alias for $2;
    firstname_in alias for $3;
    lastname_in alias for $4;
    company_in alias for $5;
    addr1_in alias for $6;
    addr2_in alias for $7;
    city_in alias for $8;
    state_in alias for $9;
    postcode_in alias for $10;
    country_in alias for $11;
    userid_calc INT4;
    companyid_calc INT4;
  BEGIN

    userid_calc := 0;

    select into companyid_calc COMPANY_ID from COMPANY
      where COMPANY_NAME = company_in;
    if (companyid_calc is null)
    then
      insert into COMPANY (COMPANY_NAME) values (company_in);
      companyid_calc := currval(''company_company_id_seq'');
    end if;

    if (companyid_calc is not null)
    then
      insert into EMPLOYEE ( COMPANY_ID ,
               AUTHORIZED , RIGHTS , USERNAME , PASSWD , FIRSTNAME ,
               LASTNAME , ADDR1 , ADDR2 , CITY , STATE , POSTCODE ,
               COUNTRY)
         values (
            companyid_calc,0,0,username_in, password_in, firstname_in,
            lastname_in, company_in,addr1_in,addr2_in,city_in,
            state_in,postcode_in,country_in
         );
      userid_calc := currval(''employee_employee_id_seq'');
    else
      rollback;
      return 0;
    end if;

    return userid_calc;

  END;'
LANGUAGE 'plpgsql';

select iu_employee('handtest','password','hand','test','handcompany',
'handaddr','handaddr2','handcity','handstate','handpostcode','handcountry');




view thread (6+ messages)  latest in thread

Message-ID: <01C08F4D.22CA2220.mdavis@sevainc.com>
Permalink:  ../01C08F4D.22CA2220.mdavis@sevainc.com/
Also on:    postgresql.org/message-id/01C08F4D.22CA2220.mdavis@sevainc.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mdavis@sevainc.com, ken@kencorey.com
  Subject: Re: Hrm...why is this wrong?
  In-Reply-To: <01C08F4D.22CA2220.mdavis@sevainc.com>
  References: <01C08F4D.22CA2220.mdavis@sevainc.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox