Am I forgetting some limitation?



I get " ERROR: syntax error at or near "$1"

SQL state: 42601 "with this



CREATE FUNCTION Import1() RETURNS text AS $$

DECLARE

path Char(100);

BEGIN

path = 'C:/zhl/hjs/anc_area.att';

COPY anc_areaAtt FROM path CVS;



RETURN path;

END;

$$ LANGUAGE plpgsql;



SELECT Import1() ;




But COPY can work well when I just put the string in the command:




CREATE FUNCTION Import2() RETURNS text AS $$

DECLARE

path Char(100);

BEGIN

COPY anc_areaAtt FROM 'C:/zhl/hjs/anc_area.att' CSV;

return path;

END;

$$ LANGUAGE plpgsql;



SELECT Import2() ;





Does this mean the "filename" in COPY command can not be a variable?



By the way, my OS is windowXP.


Thanks and Regards,
Hongliu

hongliu zou escreveu:

> Am I forgetting some limitation?
>
> I get " ERROR: syntax error at or near "$1"
>
> SQL state: 42601 "with this
>
> CREATE FUNCTION Import1() RETURNS text AS $$
> DECLARE
> path Char(100);
> BEGIN
> path = 'C:/zhl/hjs/anc_area.att';
> COPY anc_areaAtt FROM path CVS;
>

Try:
EXECUTE 'COPY anc_areaAtt FROM ' || path || ' CVS;';

>
> RETURN path;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT Import1() ;
>
>
> But COPY can work well when I just put the string in the command:
>
> CREATE FUNCTION Import2() RETURNS text AS $$
> DECLARE
> path Char(100);
> BEGIN
> COPY anc_areaAtt FROM 'C:/zhl/hjs/anc_area.att' CSV;
> return path;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT Import2() ;
>
>
> |Does this mean the "filename" in COPY command can not be a variable?|
>


Look Executing Dynamic Commands at:
http://www.postgresql.org/docs/8.2/i...-EXECUTING-DYN

[]s
Osvaldo




__________________________________________________ _____
Yahoo! Mail - Sempre a melhor opção para você!
Experimente já e veja as novidades.
http://br.yahoo.com/mailbeta/tudonovo/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br> writes:
>> |Does this mean the "filename" in COPY command can not be a variable?|

Got it in one.

You can use EXECUTE to put together commands that require a variable
in places where PG doesn't allow one.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org