Обсуждение: creating a function in psql

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

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

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

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) ????

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

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: