From adarsh.sharma@orkash.com Thu Feb  3 08:53:46 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 9B41C1337B84
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu,  3 Feb 2011 04:53:46 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 56835-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu,  3 Feb 2011 08:53:40 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp182.iad.emailsrvr.com (smtp182.iad.emailsrvr.com
	[207.97.245.182])
	by mail.postgresql.org (Postfix) with ESMTP id 21D501337B77
	for <pgsql-admin@postgresql.org>; Thu,  3 Feb 2011 04:53:39 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by smtp48.relay.iad1a.emailsrvr.com (SMTP Server) with ESMTP id
	074F9168723; Thu,  3 Feb 2011 03:53:39 -0500 (EST)
X-Virus-Scanned: OK
Received: by smtp48.relay.iad1a.emailsrvr.com (Authenticated sender:
	adarsh.sharma-AT-orkash.com) with ESMTPSA id 35E34168738
	for <pgsql-admin@postgresql.org>; Thu,  3 Feb 2011 03:53:37 -0500 (EST)
Message-ID: <4D4A6E15.7080701@orkash.com>
Date: Thu, 03 Feb 2011 14:27:57 +0530
From: Adarsh Sharma <adarsh.sharma@orkash.com>
User-Agent: Thunderbird 2.0.0.22 (X11/20090625)
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Select Command in Procedures
Content-Type: multipart/alternative;
	boundary="------------060403000209080903040605"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201102/7
X-Sequence-Number: 36646

This is a multi-part message in MIME format.
--------------060403000209080903040605
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dear all,

I am able to execute command successfully through CLI or pgAdmin .

*create table user_news_new as select 
record_id,field_name,field_value,news_date from user_news where 
field_name in ('SOI','RelLoc','Description','Heading','news_date') and 
field_value != '' ; *

But I don't know when I put this command in a procedure, it shows error :

CREATE FUNCTION user10() RETURNS void AS'
DECLARE
BEGIN
create table user_news_new as select 
record_id,field_name,field_value,news_date from user_news where 
field_name in ('SOI','RelLoc','Description','Heading','news_date') and 
field_value != '' ;
END;
' LANGUAGE 'plpgsql';


Error :

globe=# CREATE FUNCTION user10() RETURNS void AS'
globe'# DECLARE
globe'# BEGIN
globe'# create table user_news_new as select 
record_id,field_name,field_value,news_date from user_news where 
field_name in ('SOI','RelLoc','Description','Heading','news_date') and 
field_value != '' ;
globe'# END;
globe'# ' LANGUAGE 'plpgsql';
ERROR:  syntax error at or near "SOI"
LINE 4: ...ue,news_date from user_news where field_name in ('SOI','RelL...
                                                             ^

Please help as I stuck with this issue.


Thanks & Regards

Adarsh Sharma

--------------060403000209080903040605
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
Dear all,<br>
<br>
I am able to execute command successfully through CLI or pgAdmin .<br>
<br>
<b>create table user_news_new as select
record_id,field_name,field_value,news_date from user_news where
field_name in ('SOI','RelLoc','Description','Heading','news_date') and
field_value != '' ; </b><br>
<br>
But I don't know when I put this command in a procedure, it shows error
:<br>
<br>
CREATE FUNCTION user10() RETURNS void AS'<br>
DECLARE<br>
BEGIN<br>
create table user_news_new as select
record_id,field_name,field_value,news_date from user_news where
field_name in ('SOI','RelLoc','Description','Heading','news_date') and
field_value != '' ;<br>
END;<br>
' LANGUAGE 'plpgsql';<br>
<br>
<br>
Error :<br>
<br>
globe=# CREATE FUNCTION user10() RETURNS void AS'<br>
globe'# DECLARE<br>
globe'# BEGIN<br>
globe'# create table user_news_new as select
record_id,field_name,field_value,news_date from user_news where
field_name in ('SOI','RelLoc','Description','Heading','news_date') and
field_value != '' ;<br>
globe'# END;<br>
globe'# ' LANGUAGE 'plpgsql';<br>
ERROR:&nbsp; syntax error at or near "SOI"<br>
LINE 4: ...ue,news_date from user_news where field_name in
('SOI','RelL...<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ^<br>
<br>
Please help as I stuck with this issue.<br>
<br>
<br>
Thanks &amp; Regards<br>
<br>
Adarsh Sharma
</body>
</html>

--------------060403000209080903040605--



From chris@chrullrich.net Thu Feb  3 09:34:34 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id AF8481337B6E
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu,  3 Feb 2011 05:34:34 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 70218-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu,  3 Feb 2011 09:34:27 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from chrullrich.net (v29774.1blu.de [88.84.143.174])
	by mail.postgresql.org (Postfix) with ESMTP id D53951337B68
	for <pgsql-admin@postgresql.org>; Thu,  3 Feb 2011 05:34:26 -0400 (AST)
Received: from [217.31.70.82] (helo=[192.168.92.135])
	by chrullrich.net with esmtpsa (TLSv1:CAMELLIA256-SHA:256)
	(Exim 4.69) (envelope-from <chris@chrullrich.net>)
	id 1PkvZv-0002Zq-A0; Thu, 03 Feb 2011 10:34:15 +0100
Message-ID: <4D4A7696.8030802@chrullrich.net>
Date: Thu, 03 Feb 2011 10:34:14 +0100
From: Christian Ullrich <chris@chrullrich.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB;
	rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
CC: Adarsh Sharma <adarsh.sharma@orkash.com>
Subject: Re: Select Command in Procedures
References: <4D4A6E15.7080701@orkash.com>
In-Reply-To: <4D4A6E15.7080701@orkash.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201102/8
X-Sequence-Number: 36647

* Adarsh Sharma wrote:

> I am able to execute command successfully through CLI or pgAdmin .
>
> *create table user_news_new as select
> record_id,field_name,field_value,news_date from user_news where
> field_name in ('SOI','RelLoc','Description','Heading','news_date') and
> field_value != '' ; *
>
> But I don't know when I put this command in a procedure, it shows error :
>
> CREATE FUNCTION user10() RETURNS void AS'
> DECLARE
> BEGIN
> create table user_news_new as select
> record_id,field_name,field_value,news_date from user_news where
> field_name in ('SOI','RelLoc','Description','Heading','news_date') and
> field_value != '' ;
> END;
> ' LANGUAGE 'plpgsql';

You are using single quotation marks around your function body already. 
Use dollar quoting instead.

-- 
Christian