Re: Can quoting of table names be avoided?

[Fatih Genç]

Hi Jeremy,

Thank you very much for your reply, everything is ok now.


Jeremy Evans , 15 May 2018 Sal, 17:52 tarihinde
şunu yazdı:

> On Monday, May 14, 2018 at 10:57:14 PM UTC-7, Fatih Genç wrote:
>>
>> Hi,
>>
>> I'm tring to build a query with more than 1 table as fallows
>>
>> ds =
>> DB.select("r.year,r.prod,r.type,r.amnt,r.ccur,r.dcnt,r.dbcr,r.rcdt,r.stat
>> ,p.code as partner_code,p.desc as partner_desc,p.txid")
>> ds = ds.from("#{Rank.table_name.to_s} r","#{Partner.table_name.to_s}
>> p")
>> ds = ds.where(Sequel.lit('r.ptid = p.id'))
>>
>> ds.first returns an error "no such table: ranks r"
>>
>
>> What should I do to fix this?
>>
>
> Probably use Sequel's API to represent the objects:
>
> ds = DB.from(Sequel.as(Rank.table_name, :r), Sequel.as(Partner.table_name,
> :p)).
>   select{[r[:year], r[:prod], r[:type], r[:amnt], r[:ccur], r[:dcnt],
> r[:dbcr], r[:cdrt], r[:stat], p[:code].as(:partner_code),
> p[:desc].as(:partner_desc), p[:txid]]}.
>   where{{r[:ptid]=>p[:id]}}
>
> You could also just use Sequel.lit around each argument to select and from.
>
> Thanks,
> Jeremy
>
> --
> You received this message because you are subscribed to the Google Groups
> "sequel-talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sequel-talk+unsubscr...@googlegroups.com.
> To post to this group, send email to sequel-talk@googlegroups.com.
> Visit this group at https://groups.google.com/group/sequel-talk.
> For more options, visit https://groups.google.com/d/optout.
>


-- 
Fatih Genç

-BEGIN PGP PUBLIC KEY BLOCK-

mQINBFlsSrwBEADt1lDF9feJBGZIMr03JF26ajU+k84LI3rrTwMfXiCJGvjs+oH6
z7Wo80C9uMcr37XNinU6Bnt/7KwpJgDvljz1VLTpZBkLWpYkKddN0OO6xAv85Qbj
P4NS7I+EdW/ixXFB/61BFJ8MEyLzUm5qCdA26cO0ZutQSnIiGrt+A48J8kgVey0v
3u3M8S0Oq0H9WlDLIqzqdyI1lmeTwV9EHov17NIJg4Y9mmc1ZbZhyuZXIPvRz1gF
Zc2R83tnH9Fb4F24ixjuxU5HUHzcD0XmpO4JLMPDhTSbnmfw+OKf4nEEP6KtPDlF
VTpXEZPQJl4Lm8H2ywCV8QKKFf0pZ0I/WMy6ag3IsSqDL3MaEKxtUEP3zqc/63aq
UScXYgMbjAlzjVP1lTIR9lolJulMWnDmALzY4TlM5ew1g//f/qBs1ryA6jICzxnB
is4qbJXVvCFJhsGq2eigNlIA0xtZhfhrvTDc51g8vDxexXIcWDeM/EORe1NQWOH/
xvwqDtr3T13k6bWvxQSSPBWpxKdQRZ4EQmjlXDGeRlymlbov1kUSmoUoqv0mKKPm
yfkSKN5QH3rTZvnRSLkFVCcZ+edwjzOfxg5eZykIDntlBF+iAsyB4/F5upvlUakd
pXLy29YHgjFip87JYYa5IIo4AczKzc0k9p4xkVfOXYzuba/qLrmZrQkqvQARAQAB
tCBmYXRpaCBnZW7DpyA8ZmF0aWhnbmNAZ21haWwuY29tPokCTgQTAQgAOBYhBHeJ
stSlzVnYjmmJV32AiQeT06JPBQJZbEq8AhsDBQsJCAcCBhUICQoLAgQWAgMBAh4B
AheAAAoJEH2AiQeT06JPQRwQAM/A97EbKozltpAuowsSvlsI1PpKRLFg8WQ1Ce3Q
l4gcZ6tEhUWxJwBC8nnHzjgUQCkZ1seLLCaCIm4qGrDsbEH/i7/B9fNd9YJCRptx
0Ou0sxH2E+/d05jghzq/5LMajwzG1klIsGKi5KqxU9b4W6u68W2GMyICtbFgZXn1
VTHqG9Osp3AXa9urBacNHBUok46kI8braU4VKRaMluPL/R7ZK2zDPF6w7xcmEwGY
rcg3EzH0RSCNeYmnVFkIRaWLpDi5hPV3Lolge5tRi89TdZTCgOXHIg63j1pNf5xC
Ey9+Pr38+F+f5zrtJ/4mcSO24j7gLkgxc01x5CwbWG4byN6Qfj5vrWo55W7vdNKL
8cOV3AHANFsb/wSUFm3msRb5etlvgGB3NtPhiaexm/Mr4z1U7rU1SRggOBmiMpyi
tNdWuKMpbdrVvtMjFu7vTjfHBGqKCXdd0Db0mp6BaFN1c2MAhwTxPC1TAAFWv91b
NzTRIxv/iGjQ6Gfl19hFUWpifpSzNF+YFiTennHalgODeU5v1JjxSLh33l35Xh9o
KFZ9AXVnLzLtawLE4q6d5+vnLMf+czN1W8TnZSpCsGJenTLmtIeO+osy1+EG0a7L
XFxlYmet8qcsBSc+7QxuUT43NXIC9IrO1y8C8kgCbg/KSnM0qfzvTQh7GNHFXzaw
9rshuQINBFlsSrwBEACpAz5CaP81lw84ZlVF7UHBx60jrE/uAXjWZ8ET1a1ldlAF
Sx25vr5Ay+VzquoTXVL3rQ5PZBXT9ewbyQYrEUz1GplJR/VCjJL45PYca/nbu4s/
S8tLvN1SWyj45VQJqZAbbq/2R6FvbijGsqmZtPGTNklxmivpsB4bwjLGHRRJ63RS
48+zxIykCp4RNyJwyPA7pMRykicORgzO9uG6aVCfhQQFeNzh/O0hlzxfDFwvMwFR
2cz4qYtfIH+S8LY06OI+992p6fnEKBLtcjHqHxY+LtCOauvvpjony2OiHvzare4a
uqpz+dRHcykWRvsr/caV/1zBVSsVdvatebXH8qFHPKT9KYSxqowspDwISorrX5/o
we8jj6fN9v2lLS0PfCdXEL+DUjjVm34vVEv9WL1dqtuanc8lCOmfNnTBXly5HXWw
CRB7mDdrAGb/AtWujZ8rPHRVYLKvxr1bUxr4VLoeVc++wpUSFbYlaNup+Bhsl0hi
zWf8jfBQcdrFlqg2YfvaqefSZsZhYbMdgnteWs/K/umzdULLbfUORje9so0rmH3B
uul14SSE4JIDO8ZJGj3XTuEOKHAC247IgtQGwp6+HfCdm1IJpeWjl/JdTC09dQZZ
CcJBx5D6JgRLu8Tdu+TNZQ08HaskD9icSVv/SoXYMoMPSZD6R0z2yPmg0zwYTQAR
AQABiQI2BBgBCAAgFiEEd4my1KXNWdiOaYlXfYCJB5PTok8FAllsSrwCGwwACgkQ
fYCJB5PTok9b5g/+JW1NZEHNb7VoT9uahDbKMOcv1L9UeY9alBAHvbMbtBywflgp
YUnx6Mz939f59tLd9321hr1eJtjsTfPvuCMbaq0LNgh4OrxldhxTGEAO0uYKsUmC
O3y/JKws3JVn8oM/1KAzco93+RGj420dd/x9XNpUbsBFUUTWCq7xx8HB6I2O9ms5
G/T1SHJszRSIivi1XQFgU4IuDY29R5XlNpqRpwAfnZQ9iszIXq9z2PbMiQRtuNp/
RK1mC+zNehaTFwZzhLaRGZl4qjANM8nu8sTHUTho2LBM0VE0KEd00TxchG34bssL
UF4z2lmpbLLHf8qwvA99NnNffqRMbHjIU/N0nDUuyu87A94MeiCu3WXKhbkZ6qM6
fKfCrfgRcnCxiOqqzqmgaWGBH9oKopJ+QYP4F8QbvtVxaEMQQgyLR8CShZXuWc7H
S9+O8HVYycMuKkxVbgwKFBOUkGG+ZUMN2fJjXnINzrSILLw7jj7xigINE64Ra2UF
5aXKZ1jI9B4CSswu0DX4KFe/3Zt50FSd7R2erp7BoOrirZAUd9/uAj6l51lhlIr1
I7sfZcx6JbFt1WgBlURcicmvZ9JzpqKCuDL6FbmvXLafI3Av1f7Vj7+6W2BwNsvq
1f0Dd7ErIGrs5tgSusHzb6lVDE9oxrheVTrPJ6Hr68NFKcDkA1CuyaPji9E=
=MNMk
-END PGP PUBLIC KEY BLOCK-

-- 
You received this message because you are subscribed to the Google Groups 
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to 

Re: Can quoting of table names be avoided?

[Jeremy Evans]

On Monday, May 14, 2018 at 10:57:14 PM UTC-7, Fatih Genç wrote:
>
> Hi,
>
> I'm tring to build a query with more than 1 table as fallows
>
> ds = 
> DB.select("r.year,r.prod,r.type,r.amnt,r.ccur,r.dcnt,r.dbcr,r.rcdt,r.stat 
> ,p.code as partner_code,p.desc as partner_desc,p.txid")
> ds = ds.from("#{Rank.table_name.to_s} r","#{Partner.table_name.to_s} 
> p")
> ds = ds.where(Sequel.lit('r.ptid = p.id'))
>
> ds.first returns an error "no such table: ranks r"
>

> What should I do to fix this?
>

Probably use Sequel's API to represent the objects:

ds = DB.from(Sequel.as(Rank.table_name, :r), Sequel.as(Partner.table_name, 
:p)).
  select{[r[:year], r[:prod], r[:type], r[:amnt], r[:ccur], r[:dcnt], 
r[:dbcr], r[:cdrt], r[:stat], p[:code].as(:partner_code), 
p[:desc].as(:partner_desc), p[:txid]]}.
  where{{r[:ptid]=>p[:id]}} 

You could also just use Sequel.lit around each argument to select and from.

Thanks,
Jeremy

-- 
You received this message because you are subscribed to the Google Groups 
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.

Re: Can quoting of table names be avoided?

[Fatih Genç]

Hi,

I'm tring to build a query with more than 1 table as fallows

ds = 
DB.select("r.year,r.prod,r.type,r.amnt,r.ccur,r.dcnt,r.dbcr,r.rcdt,r.stat 
,p.code as partner_code,p.desc as partner_desc,p.txid")
ds = ds.from("#{Rank.table_name.to_s} r","#{Partner.table_name.to_s} p")
ds = ds.where(Sequel.lit('r.ptid = p.id'))

ds.first returns an error "no such table: ranks r"

What should I do to fix this?


On Monday, September 12, 2016 at 12:25:58 AM UTC+3, Jeremy Evans wrote:
>
> On Sunday, September 11, 2016 at 2:22:32 PM UTC-7, Will Koffel wrote:
>>
>> I have the following:
>>
>> (byebug) puts DB.from(:users).select("email").sql
>>
>> SELECT 'email' FROM "users"
>>
>> The problem is that the DB doesn't accept that SQL, because the table 
>> name is quoted.
>>
>> (byebug) DB[DB.from(:users).select("email").sql].each { |r| puts r }
>>
>> *** Sequel::DatabaseError Exception: PG::UndefinedTable: ERROR: 
>>  relation "users" does not exist
>>
>> The query works just fine manually if the table name is bare.
>>
>> The DB is RedShift.  I don't have a PG database hooked up to test with, 
>> but maybe this is a quirk of Redshift that the redshift adapter doesn't 
>> handle properly?
>>
>> I feel like I'm missing something obvious here.  Is there any way to get 
>> the table name not to be quoted?
>>
>  
> DB.quote_identifiers = false
>
> Thanks,
> Jeremy
>

-- 
You received this message because you are subscribed to the Google Groups 
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.

Re: Can quoting of table names be avoided?

[Matt Palmer]

On Sun, Sep 11, 2016 at 10:02:11PM -0700, Jeremy Evans wrote:
> On Sunday, September 11, 2016 at 7:12:24 PM UTC-7, Matt Palmer wrote:
> >
> > On Sun, Sep 11, 2016 at 11:05:17AM -0700, Will Koffel wrote: 
> > > I have the following: 
> > > 
> > > (byebug) puts DB.from(:users).select("email").sql 
> > > 
> > > SELECT 'email' FROM "users" 
> > > 
> > > The problem is that the DB doesn't accept that SQL, because the table 
> > name 
> > > is quoted. 
> > > 
> > > (byebug) DB[DB.from(:users).select("email").sql].each { |r| puts r } 
> > > 
> > > *** Sequel::DatabaseError Exception: PG::UndefinedTable: ERROR: 
> >  relation 
> > > "users" does not exist 
> > > 
> > > The query works just fine manually if the table name is bare. 
> >
> > Does the query fail as expected when you quote the table name? 
> >
> > > The DB is RedShift.  I don't have a PG database hooked up to test with, 
> > but 
> > > maybe this is a quirk of Redshift that the redshift adapter doesn't 
> > handle 
> > > properly? 
> >
> > I'd say this is a quirk of redshift: 
> >
> > => create table users (id int); 
> > CREATE TABLE 
> > => select 'email' from "users"; 
> >  ?column? 
> >  -- 
> >  (0 rows) 
> >
> 
> Actually, it's not.  You are selecting a string expression (single quotes) 
> without an alias, so the ?column? is expected, and there are no rows 
> because you just created the table.

Sorry, I wasn't clear: that example was given on a PostgreSQL 9.4 server. 
It demonstrates that the query generated by Sequel (that I executed) is
valid syntax *for PostgreSQL*; thus, if the same query fails with `ERROR:
relation "users" does not exist` in redshift, then that is a
redshift-specific quirk.

- Matt

-- 
You received this message because you are subscribed to the Google Groups 
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.

Re: Can quoting of table names be avoided?

[Jeremy Evans]

On Sunday, September 11, 2016 at 7:12:24 PM UTC-7, Matt Palmer wrote:
>
> On Sun, Sep 11, 2016 at 11:05:17AM -0700, Will Koffel wrote: 
> > I have the following: 
> > 
> > (byebug) puts DB.from(:users).select("email").sql 
> > 
> > SELECT 'email' FROM "users" 
> > 
> > The problem is that the DB doesn't accept that SQL, because the table 
> name 
> > is quoted. 
> > 
> > (byebug) DB[DB.from(:users).select("email").sql].each { |r| puts r } 
> > 
> > *** Sequel::DatabaseError Exception: PG::UndefinedTable: ERROR: 
>  relation 
> > "users" does not exist 
> > 
> > The query works just fine manually if the table name is bare. 
>
> Does the query fail as expected when you quote the table name? 
>
> > The DB is RedShift.  I don't have a PG database hooked up to test with, 
> but 
> > maybe this is a quirk of Redshift that the redshift adapter doesn't 
> handle 
> > properly? 
>
> I'd say this is a quirk of redshift: 
>
> => create table users (id int); 
> CREATE TABLE 
> => select 'email' from "users"; 
>  ?column? 
>  -- 
>  (0 rows) 
>

Actually, it's not.  You are selecting a string expression (single quotes) 
without an alias, so the ?column? is expected, and there are no rows 
because you just created the table.

Thanks,
Jeremy

-- 
You received this message because you are subscribed to the Google Groups 
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.

Re: Can quoting of table names be avoided?

[Matt Palmer]

On Sun, Sep 11, 2016 at 11:05:17AM -0700, Will Koffel wrote:
> I have the following:
> 
> (byebug) puts DB.from(:users).select("email").sql
> 
> SELECT 'email' FROM "users"
> 
> The problem is that the DB doesn't accept that SQL, because the table name 
> is quoted.
> 
> (byebug) DB[DB.from(:users).select("email").sql].each { |r| puts r }
> 
> *** Sequel::DatabaseError Exception: PG::UndefinedTable: ERROR:  relation 
> "users" does not exist
> 
> The query works just fine manually if the table name is bare.

Does the query fail as expected when you quote the table name?

> The DB is RedShift.  I don't have a PG database hooked up to test with, but 
> maybe this is a quirk of Redshift that the redshift adapter doesn't handle 
> properly?

I'd say this is a quirk of redshift:

=> create table users (id int);
CREATE TABLE
=> select 'email' from "users";
 ?column?
 --
 (0 rows)

http://docs.aws.amazon.com/redshift/latest/dg/r_names.html suggests this
*should* be supported, at least in some way.  I'm a bit leery of the
sentence, "If you use a delimited identifier, you must use the double
quotation marks for every reference to that object.", which suggests that
you would need create the table as being named `"users"`, rather than just
`users`, if you wanted it to work with quotes later, but that might be an
imprecision in the phrasing, rather than what is literally meant.

- Matt

-- 
You received this message because you are subscribed to the Google Groups 
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.

Re: Can quoting of table names be avoided?

[Jeremy Evans]

On Sunday, September 11, 2016 at 2:22:32 PM UTC-7, Will Koffel wrote:
>
> I have the following:
>
> (byebug) puts DB.from(:users).select("email").sql
>
> SELECT 'email' FROM "users"
>
> The problem is that the DB doesn't accept that SQL, because the table name 
> is quoted.
>
> (byebug) DB[DB.from(:users).select("email").sql].each { |r| puts r }
>
> *** Sequel::DatabaseError Exception: PG::UndefinedTable: ERROR:  relation 
> "users" does not exist
>
> The query works just fine manually if the table name is bare.
>
> The DB is RedShift.  I don't have a PG database hooked up to test with, 
> but maybe this is a quirk of Redshift that the redshift adapter doesn't 
> handle properly?
>
> I feel like I'm missing something obvious here.  Is there any way to get 
> the table name not to be quoted?
>
 
DB.quote_identifiers = false

Thanks,
Jeremy

-- 
You received this message because you are subscribed to the Google Groups 
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.