Postgres Pro
Downloads
Home   >   mailing lists

Thread: creating a function in psql

creating a function in psql

From
rachel cox
Date:
hi,

i'm trying to create a very basic function in psql and i keep getting
the error:
QUERY: CREATE FUNCTION add_one () RETURNS int4 AS '
    BEGIN
        RETURN 1 + 1;
    END;
' LANGUAGE 'plpgsql';
ERROR:  Unrecognized language specified in a CREATE FUNCTION:
'plpgsql'.  Recognized languages are sql, C, internal and the created
procedural languages.

does anyone know how i can get this language to be recognized?

also, this is probably related, i was trying to set the date string
using
SET DATESTYLE

and it doesn't seem to know what i'm talking about.

can anyone help??? thanks so much in advance,

rachel

Re: creating a function in psql

From
Julie Hunt
Date:
Hi
I've just done the very same thing today and found how to add plpgsql
into the pg_languages.
Firstly I located where plpgsql was on our server.
In that directory there was a file called install which gives
instructions using make file and then adding the language to your
pg_language table.
It also gives instructions on how to add it to the template1 so all
created databases will include the language.
I found that I didn't need to use makefile and was able to just do the
psql dbname <mklang.sql

This was the instruction and location of our plpgsql:-

psql test
</usr/ports/databases/postgresql/work/postgresql-6.5.2/src/pl/plpgsql/src/mklang.sql

Once this is created successfully, then you can write the functions
using plpgsql.

Hope this helps

Regards,
Julie

rachel cox wrote:

> hi,
>
> i'm trying to create a very basic function in psql and i keep getting
> the error:
> QUERY: CREATE FUNCTION add_one () RETURNS int4 AS '
>     BEGIN
>         RETURN 1 + 1;
>     END;
> ' LANGUAGE 'plpgsql';
> ERROR:  Unrecognized language specified in a CREATE FUNCTION:
> 'plpgsql'.  Recognized languages are sql, C, internal and the created
> procedural languages.
>
> does anyone know how i can get this language to be recognized?
>
> also, this is probably related, i was trying to set the date string
> using
> SET DATESTYLE
>
> and it doesn't seem to know what i'm talking about.
>
> can anyone help??? thanks so much in advance,
>
> rachel

Re: creating a function in psql

From
Julie Hunt
Date:
I use set datestyle to 'iso';
or set datestyle to 'European';
with success.

rachel cox wrote:

> also, this is probably related, i was trying to set the date string
> using
> SET DATESTYLE
>
> and it doesn't seem to know what i'm talking about.
>
> can anyone help??? thanks so much in advance,
>
> rachel

Functions in postgres

From
Victor Manuel Jaquez Leal
Date:
Hi!

I know that with \df you can see the functions available in postgres, but
there must be others not documented just like getpgusername().

My question is if are there a more complete list of postgres'
functions.  To be more specific I'm looking for a crypt function.

BTW

How do I implement a function in C that returns a varchar...

create function pgcrypt(opaqe) returns varchar....???

char *pgcrypt(char *text) ????

Re: Functions in postgres

From
"Ross J. Reedstrom"
Date:
On Wed, Apr 05, 2000 at 12:04:49PM -0500, Victor Manuel Jaquez Leal wrote:
>
> Hi!

Hi back at ya.

>
> I know that with \df you can see the functions available in postgres, but
> there must be others not documented just like getpgusername().
>
> My question is if are there a more complete list of postgres'
> functions.  To be more specific I'm looking for a crypt function.
>

Then you're in luck. Not as much luck as if there was a built in, but
I've attached my implementation below. I stole a general boiler plate
function from someone else, and modified it to call crypt. The trickiest
part was generating random salt. I use it with these SQL statements:

CREATE FUNCTION "sqlcrypt" (text,text ) RETURNS text AS
'/usr/local/pgsql/data/sqlcrypt.so' LANGUAGE 'C';

CREATE FUNCTION "sqlcrypt" (text ) RETURNS text AS 'select
sqlcrypt($1,'''')' LANGUAGE 'SQL';

That way, I can say sqlcrypt('somestring') and it'll return a crypted
version of the string, with a randomly selected salt. I use it for
storing passwords for a web based login: for that, we check logins as
so:

SELECT * FROM "Personnel" WHERE "PerUsername" = 'RJReedstrom' AND
"PerPassword" = sqlcrypt('password',substr("PerPassword",1,2))

That will only return results if the password hashes match. It does expose
the cleartext of the password between the web server and postgres db:
That's not a problem for us, since they're on the same machine.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005
Attachment

Re: Functions in postgres

From
"Ross J. Reedstrom"
Date:
Ah, I forget to mention how to compile the code I sent. I use:

gcc -fPIC -shared -I /usr/local/pgsql/include -L /usr/local/pgsql/lib
    -o sqlcrypt.so sqlcrypt.c

then move the sqlcrypt.so file into my pgsql storage space. This is
on Linux, if it matters.

Ross

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005
On Wed, Apr 05, 2000 at 12:04:49PM -0500, Victor Manuel Jaquez Leal wrote: