Re: [SQL] HOW TO HANDLE ZEROS IN DATE FIELD?

[Michael Glaesemann]

On Sep 3, 2004, at 11:36 PM, James M Doherty wrote:
I have a project that is taking input from another system. I have
certain columns defined as 'Date' Columns. On input I will get 
'00' in this field which causes the insert to fail.

00 is not a valid date, as you are aware :) This is often solved in
the application layer by preprocessing the data, changing 00 to
NULL, for example. You may be able to do some of this preprocessing in
the database itself, first loading the raw data into a temporary table
and then transforming it before putting it into the desired table.
I've CC'd the list as well.
On Sep 11, 2004, at 9:44 PM, James M Doherty @ jdoherty.net wrote:
	I was hoping I could do something in my insert trigger to change it 
on the
way in and avoid code in client applications. The reason for this is 
there
are
many client application which will be doing these inserts.
In that case I would recommend either using temp tables to process the 
data, or build a middleware app that other client apps would connect 
to, rather than to the db directly, the goal being to have a single API 
against which you can code the client apps.

Good luck!
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] PL/pgSQL Function Problem

[Michalis Kabrianis]

the inquirer wrote:
I am trying to create a function that creates a user
and adds a row to a table.  It produces no warnings or
errors when I create the function but when I attempt
to execute it I get a syntax error.  I do not
understand why this is happening.  Any help would be
greatly appreciated.
SELECT create_author( 'name', 'username', 'password'
);
ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "create_author" line 7 at
SQL statement
Here is the code:
CREATE OR REPLACE FUNCTION create_author (
VARCHAR(32), VARCHAR(32), VARCHAR(32) ) 
RETURNS INTEGER AS '
	DECLARE
		name_		ALIAS FOR $1;
		username_	ALIAS FOR $2;
		password_	ALIAS FOR $3;
		authorid_	INTEGER;
	BEGIN
		CREATE USER username_ WITH ENCRYPTED PASSWORD
password_ IN GROUP authors;

		INSERT INTO Authors 
			( Name, Username ) 
		VALUES 
			( $1, $2 );
		SELECT Max( AuthorID ) INTO authorid_ FROM Authors;

RETURN authorid_;

END;
' LANGUAGE 'plpgsql'
SECURITY INVOKER
RETURNS NULL ON NULL INPUT;
		
As Tom Lane said before me, use EXECUTE.
I have that on a similar project
CREATE FUNCTION s_user() RETURNS "trigger"
AS '
DECLARE
   uname text;
BEGIN
   uname := ''s'' || NEW.code::character varying;
   EXECUTE ''CREATE USER ''||uname||'' WITH ENCRYPTED PASSWORD 
pass NOCREATEDB NOCREATEUSER IN GROUP salesmen;'';
   RETURN NEW;
END
'
LANGUAGE plpgsql SECURITY DEFINER;

It's obviously is a trigger on an insert on some table, and creates the 
username based on that tables' primary key. It also sets a standard 
password, to be canged by the user.

I use it with SECURITY DEFINER because users that use that piece of code 
are ordinary users and don't have the right to create users in any other 
way.

Michalis
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] [GENERAL] PL/pgSQL Function Problem

[Doug McNaught]

the inquirer <[EMAIL PROTECTED]> writes:

> ERROR: syntax error at or near "$1" at character 14
> CONTEXT: PL/pgSQL function "create_author" line 7 at
> SQL statement

PL/pgSQL errors are sometimes obscure.  I'll try to comment on what
looks wrong to me...

> CREATE OR REPLACE FUNCTION create_author (
> VARCHAR(32), VARCHAR(32), VARCHAR(32) ) 
> RETURNS INTEGER AS '
>   DECLARE
>   name_   ALIAS FOR $1;
>   username_   ALIAS FOR $2;
>   password_   ALIAS FOR $3;
>   authorid_   INTEGER;
>   BEGIN
>   CREATE USER username_ WITH ENCRYPTED PASSWORD
> password_ IN GROUP authors;

I can't tell you why, but my suspicion is that you need to use EXECUTE
in order to do CREATE USER in a function.  Try that. 

>   INSERT INTO Authors 

Be aware that, unless you double-quote the identifier, "Author" will
be folded to "author" by the SQL parser.  This may also be causing a
problem--you didn't post your schema.

>   ( Name, Username ) 
>   VALUES 
>   ( $1, $2 );
>   SELECT Max( AuthorID ) INTO authorid_ FROM Authors;

This is a potential race, depending on your transaction isolation
level.  It would be better to find out the sequence name for your
SERIAL column and user currval('') as the return value.

Hope this helps!

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] create unique index schema.index_name on table (column)?

[m0ntar3]

Is the syntax "schema_name.index_name" for create unique index wrong, 
unsupported or what? I know is doesn't work as postgres kicks me back a 
syntax error each time (version 7.3.2 & 7.4.5). -sigh-

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] create unique index schema.index_name on table (column)?

[Michael Glaesemann]

On Sep 12, 2004, at 5:42 AM, [EMAIL PROTECTED] wrote:
Is the syntax "schema_name.index_name" for create unique index wrong, 
unsupported or what? I know is doesn't work as postgres kicks me back 
a syntax error each time (version 7.3.2 & 7.4.5). -sigh-
Could you give a full example (including the error) of what you're 
doing? It's kind of hard to give a suggestion based on your brief 
description.

Michael
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] create unique index schema.index_name on table (column)?

[Michael Glaesemann]

On Sep 12, 2004, at 2:48 PM, Michael Glaesemann wrote:
On Sep 12, 2004, at 5:42 AM, [EMAIL PROTECTED] wrote:
Is the syntax "schema_name.index_name" for create unique index wrong, 
unsupported or what? I know is doesn't work as postgres kicks me back 
a syntax error each time (version 7.3.2 & 7.4.5). -sigh-
Could you give a full example (including the error) of what you're 
doing? It's kind of hard to give a suggestion based on your brief 
description.
Also, looking quickly at the online docs, we find :
name
The name of the index to be created. No schema name can be included 
here; the index is always created in the same schema as its parent 
table.

Michael
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] create unique index schema.index_name on table (column)?

[Tom Lane]

[EMAIL PROTECTED] writes:
> Is the syntax "schema_name.index_name" for create unique index wrong, 
> unsupported or what?

Yes.

Put the schema name on the table, instead.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]